All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI

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.

Video

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:

Do you need a Date Dimension?

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;

Default Vs. Custom Date Dimension in Power BI

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.

Script

This is the DAX script for the Date dimension:

Configuration

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;

Sample Records

Here are some sample records of this table:

Considerations

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.

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.