In two previous blog posts, here and here, I gave an overview of various ways to build a calendar table in DAX. I also alluded to it being quicker to build these using Power Query or the Power BI Editor.
In this post, I will show you how easy it is to build a calendar table in Power Query using some ‘M’ code. By easy, I mean, once you have been provided with the code, it is easy to replicate. Based on this, you will not need to be an ‘M’ guru or even a novice, you can simply follow along, copy the code provided and paste it into your workbook.
Allowing the calendar table to be dynamic, based on your own dataset, requires a little customization, however, using our example, I’m sure you’ll be able to make sense of this. ;)
Why do I need a calendar table?
As a quick summary, when using DAX time intelligence functions, you need to have a date table included in your Data Model. The date table must include a column with one row for every day of each year included in your data.
We will be building a dynamic calendar table that is based on the Min and Max date of your fact table.
As mentioned, we have prepared a script that will create a date calendar dynamically as depicted above. This Power BI Query Editor script can be added to any Power BI report.
Create a date dimension function (fnDateDim)
Steps: In Power BI Desktop > Home tab > Get Data > Blank Query > in query editor > view tab > click advance editor > delete existing code with our script > click Done > rename the function to “fnDateDim”
After this, we are done with creating the function ‘fnDateDim’. Below is the script used:
let fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate – StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertQuarterNum = Table.AddColumn(InsertYear, “Quarter Num”, each Date.QuarterOfYear([Date])),
InsertQuarter = Table.AddColumn(InsertQuarterNum, “Quarter”, each “Q” & Number.ToText([Quarter Num])),
InsertMonth = Table.AddColumn(InsertQuarter, “Month Num”, each Date.Month([Date]), type text),InsertStartOfMonth = Table.AddColumn(InsertMonth, “StartOfMonth”, each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, “EndOfMonth”, each Date.EndOfMonth([Date]), type date),
InsertDay = Table.AddColumn(InsertEndOfMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year]*10000 + [Month Num]*100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “Month”, each Date.ToText([Date], “MMMM”, Culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, “Month short”, each Date.ToText([Date], “MMM”, Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, “Month Year”, each [Month short]& ” ” & Number.ToText([Year]),type text),InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “Quarter Year”, each “Q” & Number.ToText([Quarter Num]) & ” ” & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “Weekday Num”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “Weekday”, each Date.ToText([Date], “dddd”, Culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, “Weekday short”, each Date.ToText([Date], “ddd”, Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertShortDayName , “EndOfWeek”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Num”, each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, “WeekOfMonth Num”, each Date.WeekOfMonth([Date])),
InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,”Month-YearOrder”, each [Year]*10000 + [Month Num]*100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,”Quarter-YearOrder”, each [Year]*10000 + [Quarter Num]*100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“Quarter-YearOrder”, Int64.Type},{“Week Num”, Int64.Type},{“WeekOfMonth Num”, Int64.Type},{“Quarter”, type text},{“Year”, type text},{“Month-YearOrder”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“Month Num”, Int64.Type}, {“Quarter Num”, Int64.Type}, {“Weekday Num”, Int64.Type}})
in
ChangedType1
in
fnDateTable
Brief description of script:
With this function we are taking two input parameters, the start date and end date, from these, this function generates a list of continuous dates and then derives the other columns like Year, Quarter, Month, Week etc. from this date list.
The second part is the consumption of this function. To use this function, we must provide the start date and the end date. These two parameters are dynamically obtained from the ‘main’ table in our data model, e.g. fact table or transaction table, which will contain a date field.
Creating a Date Dimension Table
Steps: As per the previous step, create a blank query and paste the below script in the advance editor. Click done and then rename to this as ‘DateCalendar’ as shown below:
The script:
let
Source = fnDateDim(Date.StartOfYear(List.Min(FactSales[OrderDate])),Date.EndOfYear(List.Max(FactSales[OrderDate])), “en-us”)
in
Source
**** Please Note: The script should be pasted to the advanced editor window, not to the formula bar ****
Script description
As highlighted in blue above, we are providing the Min and Max value of the year from our fact table to the function which generates the date calendar. When following along, simply change the blue part of the script to match that of your fact table and the field containing the date.
After all steps, you will have a dynamic Date Calendar table as shown below:
Replicating a calendar table in this manner is faster than the previous methods using DAX, as shown in my previous posts. Download the sample Power BI file:
Have fun building your own dynamic date table in Power BI. :) For more great posts, see our blog page.
Excellent reference article. I’ll be book marking this. Thanks.
Thanks for the feedback
Johann, I’m working on creating one of these dynamic calendars. Something is wrong, though, and I can’t find the problem. Would you mind taking a look at this screenshot and seeing if you can spot my error?
https://www.dropbox.com/s/7trkol249ycp1zy/Screen%20Shot%202017-01-03%20at%2010.55.30%20AM.png?dl=0
Will need to check the brackets on this, if you provide me the file, I’ll check it for you.
Even when I copied the script out of your example pbix file and pasted it into mine (changing only the fact table column references), I get this error:
https://www.dropbox.com/s/126itwg6s4u7y5c/Screen%20Shot%202017-01-03%20at%2011.05.11%20AM.png?dl=0
Same problem: Expression.SyntaxError: Token Comma expected
(Copied and pasted first script above)
(FYI concerning above post– the error thing is pointing to between lines 3 and 4)
Hi Galen,
Please ensure that you are pasting the script to the advanced editor not the formula bar.
Firstly great website, and work
Unfortunately I also get token comma expected on:
DayCount = Duration.Days(Duration.From(End Date – StartDate))+1,
To fix the error messages some readers are seeing when creating this function:
The blog software converted the hyphen to an n-dash, and note that the quotation marks throughout the Function code are “smart” quotes. Just fix these items, and the function will work perfectly.
Thanks John!
That’s the ticket!
for Germans there is also an issue with the quotes…
here the working thing in pastebin: http://pastebin.com/vxRc4AnU
and THX Johann
Thanks!
Hi Johann and thank you for a great help with Power BI, you are our go-to-guy to get help :)
We created a dynamic calendar with your script – thanks a lot, but how do I add to the table? We changed the script a little bit to have the end date in this month instead of this year, we did that in March so now I don’t know how to add dates in April?
We figured this out!
Hi Steinunn,
Glad you figured it out! ;)
Hey Johann
Thanks for sharing this wonderful code…I am trying to use m code to generate the calendar in minutes….
I am able to generate the table when I specify the date in the start and end date
However if I want it to dynamically pick the start and end date..I keep getting the error:
The name”fnDateDim” wasn’t recognized. I cannot understand why because I copied the code as is
Hi,
Glad you found the code useful. I have a dynamic calendar that allows you to pick the start and end dates. I’m unable to attach files to comments so will e-mail it to you.
Hey thanks for sharing this code. It would be nice if you could create a code where it would also include the time dimension (hours and minutes) I tried to do this my self but couldn’t figure it out
Br.Niko
Hi Johann, great article. Your code is very helpful.
Thank you very much.
Pavel
Hi Pavel,
Glad you find it useful. ;)
Still using this 6 years later. Thanks for sharing!