All in One: Script to Create Date Dimension in Power BI using Power Query

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

I have written multiple blog posts about how to create a date dimension using Power Query, however, the purpose of those blog posts was to teach you how to do it yourself, learn the process and also build the date dimension that you can use. Sometimes, however, you just want to create a date dimension very fast, and you want just the script to copy and paste. So that’s what this blog post is about, here you will find the full script to create the date dimension in Power BI using Power Query that you can simply copy and paste it into your solution.

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 blank query in Power BI:

In the Power Query Editor window, go to View tab, and click on Advanced Editor

Copy and Paste the entire script here (replace the existing text in the Advanced Editor window:

Script

This is the script for the Date dimension:

Power Query Script for the Date Dimension (938 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;

Sample Records

Here are some sample records of this table:

Best Practice Suggestion

Because the Date table is a table that is needed not only in one Power BI file but in many others, I suggest creating a dataflow entity for the date table. Here in this article, I explained how you can create a dataflow.

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 a dataflow (as mentioned in the previous step).

Study More

If you like to learn more about how this table is created I suggest reading the articles below:

Create a Date Table in Power Query Step 1: Calendar Columns

Create a Date Table in Power Query Step 2: Fiscal Columns

If you are not yet sure if you need a date dimension or not, read this article:

Do you need a Date Dimension?

If you are not sure, should you be using the default date dimension, or create your custom one (like what explained here in this blog post) read this article:

Default Vs. Custom Date Dimension in Power BI

If you also want to bring public holidays into your Date dimension, read the below article:

Create a Date Table in Power Query Step 3: Public Holidays

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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.

8 thoughts on “All in One: Script to Create Date Dimension in Power BI using Power Query

  • Nice!

    I prefer end dates to be dynamic. Rarely does it need to be more than 1 year past the current year. I use

    ToDate = DateTime.Date(Date.EndOfYear(Date.AddYears(DateTime.LocalNow(),1)),

    • Hi Joris
      That is also good to generate it until the year after the current year.
      However, for some datasets that people start doing some forward planning for their next 3, 5 or even 10 years, it might need a change.
      Cheers
      Reza

  • I wish I could tell you where I got it, but we have started using this formula for our quick date dimensions. The cool part is that you can add it as a table (Modeling > New Table) and not have to go into Power Query. Just change Table[Date] to be your date column. DateDim = ADDCOLUMNS ( CALENDAR(MIN(Table[Date]),MAX(Table[Date])), “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ), “Year”, YEAR ( [Date] ), “MonthNo”, FORMAT ( [Date], “MM” ), “YearMonthNo”, FORMAT ( [Date], “YYYY-MM” ), “YearMonth”, FORMAT ( [Date], “YYYY-mmm” ), “MonthShort”, FORMAT ( [Date], “mmm” ), “MonthLong”, FORMAT ( [Date], “mmmm” ), “WeekNo”, WEEKDAY ( [Date] ), “WeekDay”, FORMAT ( [Date], “dddd” ), “WeekDayShort”, FORMAT ( [Date], “dddd” ), “Quarter”, “Q” & FORMAT ( [Date], “Q” ), “YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q” & FORMAT ( [Date], “Q” ))

  • Hi, my dates change when I publish the report to Power BI service and data associated with it also changes, the report is refreshed using a gateway on PBI service. I am using the date table with the code given above!! Is it because of DateTime.LocalNow() function which is creating problem for me?

Leave a Reply

%d bloggers like this: