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

create a calendar table in Power BI using DAX

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:

create a calculated table in Power BI

Then just copy and paste the script provided in this post there and press ENTER.

paste the DAX script to create a date dimension in Power BI

Mark the table as Date table, by right click on the table,

Mark as Date table

In the Mark as date table’s setting, select the Date column in the dropdown, and click on OK.

Mark as date table’s setting in Power BI

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:

Script for Date Dimension in Power BI using DAX - RADACAD (2095 downloads)

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;

set the start and end year, and the starting month of the fiscal year

Sample Records

Here are some sample records of this table:

sample records for the date dimension in Power BI

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply