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:
This is the script for the Date dimension:Power Query Script for the Date Dimension (75862 downloads)
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;
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.
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).
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:
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
17 thoughts on “All in One: Script to Create Date Dimension in Power BI using Power Query”
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)),
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.
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” ))
Creating date dimension using DAX is of course an option, however, I usually prefer the Power Query method, because it gives me the ability to combine it with public holidays
Would you were comparing same period last year, would you do this in DAX or Power Query?
Same Period Last Year and many other time intelligence calculations are much easier to be implemented using DAX functions
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?
You can use this approach I explained here to get your today working without any problem.
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.
can you please share screenshot of your data model, and the DAX expression you have?
The day of the week is starting from Tuesday and not Monday. How can I change it?
you can change it using a variable at the beginning of the script
I mean yes I did try this but didn’t help. I kept the value as -Day.Monday.
What did you change it to?
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?
Formatting is something you need to do in the data model AFTER loading data into Power BI. you can click on a field and set a custom format for it
Dear Reza, what an outstanding material. Thanks a lot for sharing.