Patou Tips #3 (update)
“From Rookie to Rock”
Best practices
with dates
in PowerBI
Downloadable free
resources to practice
in GitHub
1 Create a table of dates in DAX
→ The table: « DimDate »
In powerBI, create a table called
“DimDate” and write this code in DAX
Path: « Modeling > New Table »
See Tip 6 to go further
→ Create short month
See Tip 3 to go further
→ Sort month chronologically
2 Mark as a date table
This step is one of the first things to do when you create a PowerBI
project. With this step, PowerBI validates your date system and
allows optimal operation with the “Time Intelligence” functions.
Important: Your date system must contain:
✓ only unique values
✓ contiguous values
✓ no null value
On the « table
view »
Path: «Table tools >
Mark as date
table »
Click on the button
« Mark as date
table » and choose
the field « Date ».
PowerBi will validate
this entry.
3 Sort “month” chronologically
→ Same thing can be done for week and day
This tip is really usefull when you want to have a good ascending sorting
of dates; the days and months in particular.
For example, the months in PowerBI will be classified in alphabetical
order, so a year will start with the months of August, then April...???
It’s important to have a good sort to show good storytelling. So, we can
sort the months by another field order, such the month numbers
(NumMonth) who give the value 1 for January, 2 for February...
Before After
4 Create a “relative Month”
→ Also usefull with days, weeks…
This tip is really useful for your users (and also for your measures). It
allows to display only the month with sales data in the filter. In the
example below we have sales until March 15, 2024. So why to show to the
users the entire month of the year as well as subsequent years? In the
table of dates, create a calculated column and write the Dax formula below.
→ Explanation: The month of march 2024 will have the value “O (zero)”,
and before march 2024 the months will have a negative value (the past) and
positive value after march 2024 (the future). After that we put this value
in the pane filter of the “report view” and put the settings showing below.
Before After
5 Create hierarchy of date
This tip will allow you to group your main dates information and use it
simultaneously in your tables or charts. This will also make it easier
for your users to understand.
On the « Table View », right click on the « Year » label of the
DimDate and choose « Create hierarchy ». Here I rename the
hierarchy in « Date Hierarchy ». Then, click right on the « Month »
label and choose « Add to hirerchy ». You can do this for any field
that you want to add in the new hierachy. I add also the field
« Date ».
Before After
6 Create short month
It can be useful to have only the first letter of each month, especially
when you have charts in a small space. See Tip 1 for lines reference.
→ Explanation: On line 7 of the table date code (see Tip 1), we take the first
letter (LEFT) of each month in capital (UPPER).
→ Explanation: In English, for example, the first letter of a month, "J", can
be January, June or July. It is important to distinguish them if we want to
order them (as Tip 4) chronologically. It depends on the language of the
country. So, for the second "J" we put a space (" ") and 2 spaces (" ") for
the third "J“ to have different values.
7 Performance optimization
This tip will allow you to avoid having a model whose size can really
increase more and more. Also this tip will improve its performance.
Path: File > Options and settings > Options > Data Load > Time intelligence
Unselect “Auto
date/time”
Don’t forget!
This isn't the truth, it's just my truth!
Patou Tips
Follow me
Like me
Share me
From the book
“Story of a Point”
Financial forecast with PowerBi
Available in book and e-book
English : December 2025
French : January 2026
German : March 2026
