Create a date table in Power BI

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.


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


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

See also