Power BI Tip : Dynamic Calendar Table (Power Query)

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:

dynamic calendar

Have fun building your own dynamic date table in Power BI. :)  For more great posts, see our blog page.