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

Posted on by Reza Rad

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 (100051 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

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

17 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)),

    Reply

    • 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

      Reply

  • 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” ))

    Reply

  • Hi Reza,

    Would you were comparing same period last year, would you do this in DAX or Power Query?

    Thanks

    Felix

    Reply

    • Hi Felix
      Same Period Last Year and many other time intelligence calculations are much easier to be implemented using DAX functions
      Cheers
      Reza

      Reply

  • 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?

    Reply

  • Hello Reza,

    Trust you are doing great.

    Sent you a tweet as well. I am using your date table and its excellent but I seem to be struggling with time intelligence functions when I want to calculate Last Quarter, Last Month etc when I use the DatesAdd function.

    Please do you have any resource where you used these functions with your date table? Hope to read from you soon.

    Cheers,
    Simon

    Reply

  • Hi Reza, thanks for the codes. Our accounting system has a year end period using the date format 12/31/20 11:59:59 PM. How can we add those year end dates to your date table?

    Reply

Leave a Reply