Learn how to create a date table in Power BI
The challenge
Power BI already has a build in time intelligence, but when you are building more complex reports, you don’t really have any ownership of the dates.
When I am reading what other people are saying online, most people recommend that you turn off the time intelligence in Power BI
You can turn off the time intelligence globally in Power BI under settings
Or you can do it on the current file (this is what I prefer instead of globally, because if you just want to test something or make a very simple report, the time intelligence can be OK)
The solution
There are many suggestions on how you can build a date table.
These are my 2 preferred solutions.
DATE TABLE 1
Power Query (M) table (complex table)
This table is written by Melissa de Korte who is a co author of the book The Definitive Guide to Power Query
You can find the code in this forum and make a copy of the code
In Power BI select Get data -> Blank query
Rename your query to DateTable and then click on Advanced Editor
Replace the entire code in the new window, with what you just copied, and click done
Here you can insert the StartDate and EndDate, and if you are working with a company that does not have the financial year from 1/1 to 31/12, you can change that in the FYStartMonthNum
Click Invoke, and rename your new table to dimDate
Now we should combine our new date table with our fact table. In the Model view drag the field date from your dimDate to your fctTable (not the other way around, notice the way the arrow points)
We now have a very powerful date table - I will follow up on this blog how we can utilize from this table to solve otherwise complex challenges in Power BI
DATE TABLE 2
Third party plugin - Bravo
There is a third party tool called Bravo, created by sqlbi
The tool can help you to analyze your model for performance, it can format you DAX, and it can create a date table for you.
After you have downloaded the software, you can run it directly from your start menu, or you can find it inside Power BI under external tools
When opening the tool inside Power BI, it will automatically attach to your file.
The tool is very user friendly, and all you need, is that you navigate Manage Dates and select the setting you prefer.
Notice that you might want to change the Regional format under Dates otherwise you will end up with American dates
Note that you need to turn of the time intelligence in your Power BI file, before you can add a date table with Bravo
The Bravo date table is not as complex as the Power Query Editor M table, but it is a lot more user friendly to apply (and if your report is not complex, no reason to overdo anything)
Happy dating