Power BI Date or Calendar Table Best Method: DAX or Power Query?

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.

Video

The term

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.

Summary

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.