Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 |

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”

2016-11-06_10h42_36    2016-11-06_10h47_13


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 =>
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}})


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:

Source = fnDateDim(Date.StartOfYear(List.Min(FactSales[OrderDate])),Date.EndOfYear(List.Max(FactSales[OrderDate])), “en-us”)

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

17 thoughts on “Power BI Tip : Dynamic Calendar Table (Power Query)”

  1. Firstly great website, and work
    Unfortunately I also get token comma expected on:

    DayCount = Duration.Days(Duration.From(End Date – StartDate))+1,

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

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

Leave a Comment

Your email address will not be published. Required fields are marked *