I have previously written an article with the full script of how to generate a date dimension in Power BI using the Power Query script, and I would always recommend that as the first choice. However, sometimes, you just want this to be in DAX way, so here in this blog and video, I explained how to create a fully-fledged date table using a DAX script.
Introduction and background information: Date Dimension
Date Dimension or Calendar table is a table with one date at every row, and the columns representing attributes of that date (such as year, month, quarter, etc).
Do you need a date dimension?
You may firstly ask; “Do I need a date table?”. I have explained in detail why a date table is required in any BI or analytics solution, you can read more about it here:
Default Vs. Custom Date table
In Power BI, there is a default date table that you can use. Or you can build your custom date table. I have explained in detail why you may choose one over the other. read this to learn more about it;
Custom date table using Power Query
If you want to create a custom date dimension in Power BI, my first suggestion would be doing it using the Power Query. One of the reasons for this, is that you can create a dateflow entity using this date table, and re-use it in other models. Here is a blog article that I explained in details how to do that and create a full date table using Power Query;
Columns Included in the Date Dimension
This date dimension (or you might call it a calendar table) includes all the columns related to the calendar year and financial year as below;
How to use the Script?
Create a new table (calculated table) in Power BI:
Then just copy and paste the script provided in this post there and press ENTER.
Mark the table as Date table, by right click on the table,
In the Mark as date table’s setting, select the Date column in the dropdown, and click on OK.
Now your date dimension is ready, you can connect it to other tables using relationships and start slicing and dicing the data with the fields in the date table.
This is the DAX script for the Date dimension:Script for Date Dimension in Power BI using DAX - RADACAD (34229 downloads)
You need to configure the Date table based on your need, the first few lines are the configurations that you can set based on your need;
Here are some sample records of this table:
There are a few things you need to consider if you are using this script;
- This Date dimension does NOT include public holidays information if you wish to get that, use this approach.
- This Date dimension is not supporting scenarios with fiscal weeks. for those scenarios, some changes need to be applied to the script.
- If you want to use this date dimension in multiple Power BI files, consider using the Power Query version of it and a dataflow entity for the date dimension.