So you want to create a date or calendar table in Power BI, and you search for ways to do that. Then you will find many different approaches for that. Some of the approaches are using Power Query, some are using DAX. Which method is the best? What is the actual difference between a date table created using DAX or Power Query? This article is going to answer that question.
The term of Date table is used in many different forms. here are some of them;
- Date dimension
- date table
- calendar table
- calendar dimension
- time table (even though it doesn’t have time attributes in it!)
All of the tables above are the same thing. People just call it differently. It is a table with one record per date, and each column is an attribute of the date, such as Year, Month, Weekday, etc.
*Time table is different from date table. The Time table has a record per each time bucket. And I have written about it separately here. However, there are some users who call their date table a Time table.
No matter what you call the table, let’s see if you really need one?
Why Date or calendar table?
It is very common to have a date-based analysis in a reporting solution. The date-based analysis normally isn’t just by year or month, it might be by the weekday Vs. weekend, or comparing a half year with another half year. Many attributes of the Date itself can be important in data analysis. I have explained in an article in detail why you need a Date table, please read it here.
Custom or Default Date table?
For simplicity of analysis, Power BI Desktop gives you an option to create a default date table. This table includes minimal columns such as year, quarter, month, and day. This is helpful if you don’t want to create your own version of the Date table and create an analysis with the built-in default date table simply. I have explained the difference between the custom or default date table here.
The default date table, although helpful in many situations, can be sometimes problematic. Here is an example of a performance problem you may face with it. You may want to create your custom date table, and that is where you realize you can do it in both M and DAX.
DAX Date table Script
I have written an article with the full script of how to create a Date table using a DAX expression as a calculated table. All you need to do is to download the script, copy and paste it and be done! You can find more details about it here.
Power Query Date table Script
You can also create the Date table using the Power Query script. Again I have a full script of Power Query (M) ready for download here. You just need to create a blank query in Power Query Editor with the script you can download from my article here;
DAX and M; Both Capable
The date table creation isn’t rocket science. and gladly both DAX and Power Query are capable of creating it easily. That is one of the reasons when you search for the Power BI Date table you find many scripts and articles about it, and they are mostly done nicely by many of the community and Microsoft experts in the field.
Calculating the weekday from the date, or fiscal quarter or even week from a date isn’t complicated for DAX or M. They can both handle it with ease. There will be some of the Date attributes that might be just a bit easier to be created in M than DAX or the other way around. but at the end of the day, their scripts work the same. There won’t be much of a performance difference between the two. I even myself use one method over the other sometimes.
The Difference? DAX OR M?
When you look at the previous paragraph about what they can do, you kind of realize that in principle there shouldn’t be a huge difference in creating a Date table in DAX or M. And that is right. Only a few differences can be impacting your solution depends on the requirement. Before jumping into those, I suggest you read my article about DAX or M, what is the difference is in general.
When it comes to creating the Date dimension in Power BI, there are little benefits of doing it each way;
DAX: No need to go to Power Query Editor – Simplicity
One of the main benefits of creating the Date table in DAX is that you don’t need to leave the Power BI Desktop environment. Meaning you don’t need to open Power Query Editor. All you need to do is just to create a new table inside Power BI Desktop and that’s it.
Not going to Power Query Editor, makes it easier and more convenient to create the table simply by just copy and paste.
Power Query: Date table in Dataflow – Consistency
In real-world data analysis space, you will not have just one single Power BI file, It is likely that you have multiple Power BI files, and if you are going to copy and paste the script to create the Date table, you are duplicating your code, and redundancy comes with the cost of maintenance in the future. If you want to add a column to one of the Date tables, you have to do it for all of them.
However, The Date table created by a Power Query script can be easily used as a dataflow table. This means the date table, then can be re-useable to anyone who has access to that workspace and the dataflow. The re-usability is a big win when you create the Date table in the dataflow. I have explained about moving your shared tables into the dataflow in this article.
Power Query: Combining with custom data
A date table is a generic table. Most of the users, don’t create it, they just copy the script from somewhere (like the above scripts I mentioned) and then use it. And that is absolutely fine. However, there are some scenarios, in which, you need to bring some customized date configuration in the analysis. I’ll give you a couple of examples;
1- Company A wants to have reports and analysis date-based based on the company-specific working days, which might be different from normal public workdays. There is a need that the Date table to be merged with the company-specific working days as one single date table.
2- Company B not only wants to analyze their data on a date basis but is also interested to analyze data on public holidays or regional holidays too. They might be also interested in how their sales are changing in school holidays. For a scenario like this, they need to combine the Date table with some API call results of public holidays and school holidays.
The two examples above are just a few of many custom-date-based analyses. A Power Query generated date table normally works better in these situations, as it allows you to connect to another data source, bring the custom data and combine it with the date table. I explained already how this is possible for a public holidays API using Power Query.
If you want to achieve something like this in DAX, it would require a lot of work and hard coding of the values in the script, which is not ideal.
Date table in the data source
One other common way of using the Date table is to have it in the data source. Let’s say in a SQL Server database you have a date dimension with all the required columns. The table can be populated using a T-SQL Script. This table then can be used in any Power BI reports. Long time ago, I have created a script to create a T-SQL date table here.
The key to the method above, however, is to give the Power BI Developers access to this database table and let them know that this exists. Otherwise, they might go and create a Date table inside Power BI because they don’t know it is there already in the data source.
Copies of Date table: Role-playing dimension
Although, I am more inclined to create my date tables using Power Query and more specifically in the dataflow. I won’t suggest creating copies of the Date table in Power Query. Copies of the Date table are needed when you want to analyze data by multiple date fields from the same table. For example, you may have OrderDate and ShipDate in the FactSales. In scenarios like this, you can create a copy of the Date table for each of the date fields and connect it using an active relationship. This method is called the role-playing dimension.
The reason I suggest this not be done using Power Query is that Power Query will connect to the data source again for populating the copy. I suggest creating a DAX table with the ALL function just to copy this table. I explained the method in detail here.
Date table is a very common dimension table in many BI solutions. Because mostly it includes generic columns and rows, you can use a Power Query or DAX script to create it. The two methods are *almost* similar in their performance, and usages. However, there are some differences in reusability and extensibility of it, which might be depending on your requirement. Let me know your questions and thoughts in the comments below, I like to hear from you too.
6 thoughts on “Power BI Date or Calendar Table Best Method: DAX or Power Query?”
Thank you, I really liked this post, it is very comprehensive! I think a DAX based date table could be reused also with PowerBi premium by fetching the table from a dataset the same way like from a dataflow, right? You say that there is a refresh cost of making copies of the date table in PowerQuery. But we can just create them for quite a long date range and disable the refresh. In our business we don’t analyze data for public holidays and other special days so we prefer the DAX based table. We just use CALENDARAUTO() plus add a few basic attributes by using FORMAT, YEAR etc.
You CAN certainly create the date table inside a Power BI dataset, and get data using DirectQuery to Power BI dataset to use that table, yes, possible.
regarding the refresh also; If the date table is not changing (you are not using holidays, or special days, or not even offset columns), then disabling the refresh can help for sure
Great as usual! Is there an equivalent to CALENDARAUTO in M?. A company date table in Dataflow is compelling, but I would like to ensure that I get dates for the whole year for my time intelligence measures and cover my date range. And I assume that I must remember to Mark as date table and Sort by columns (i.e. for month names) for each pbix?
We do not have an exact equivalent of calendarauto in M unfortunately.
calendarauto scans the whole data model tables and all their fields find the minimum date value and the maximum date value and create ranges of full years covering those.
if you want to achieve something like that in Power Query, that needs some work and is not as simple and straightforward as calendarauto. you’ll need to get all tables and their date columns, then find the first and the last date. this means a number of data transformation steps.
Thank You, very comprehensive.
I am glad it helps