Power BI Tip : Dynamic Calendar Table (Power Query)

In a 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 a ‘M’ guru or even a novice, you can simple 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 which be based on the Min and Max date of your fact table.

2016-11-06_12h12_15

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

2016-11-06_10h58_17

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:

2016-11-06_12h04_46

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 dynamic Date Calendar table as shown below:

2016-11-06_12h07_12

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

By | 2017-02-15T23:57:11+00:00 November 8th, 2016|Power BI, Power BI Tips|20 Comments

About the Author:

Johann is the founder of Data Bear and specializes in getting the right data into the right hands, in a format that is quick and easy to understand. Revolutionizing your reporting experience from a time consuming frustration into dynamic interactive visualizations is what he takes pleasure in.

20 Comments

  1. Casey Hancock November 10, 2016 at 4:30 pm - Reply

    Excellent reference article. I’ll be book marking this. Thanks.

    • Johann November 10, 2016 at 5:21 pm - Reply

      Thanks for the feedback

  2. Casey Hancock January 3, 2017 at 3:59 pm - Reply

    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

    • Johann January 7, 2017 at 9:37 pm - Reply

      Will need to check the brackets on this, if you provide me the file, I’ll check it for you.

  3. Casey Hancock January 3, 2017 at 4:06 pm - Reply

    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

  4. Galen January 18, 2017 at 7:33 pm - Reply

    Same problem: Expression.SyntaxError: Token Comma expected
    (Copied and pasted first script above)

  5. Galen January 18, 2017 at 7:36 pm - Reply

    (FYI concerning above post– the error thing is pointing to between lines 3 and 4)

    • Johann February 15, 2017 at 11:58 pm - Reply

      Hi Galen,

      Please ensure that you are pasting the script to the advanced editor not the formula bar.

  6. tim February 14, 2017 at 11:11 am - Reply

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

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

  7. John February 27, 2017 at 11:24 pm - Reply

    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.

    • Johann March 3, 2017 at 8:47 pm - Reply

      Thanks John!

    • Galen March 3, 2017 at 9:43 pm - Reply

      That’s the ticket!

  8. cs_skit March 20, 2017 at 3:30 pm - Reply

    for Germans there is also an issue with the quotes…

    here the working thing in pastebin: http://pastebin.com/vxRc4AnU

    and THX Johann

    • Johann March 24, 2017 at 8:54 pm - Reply

      Thanks!

  9. Steinunn April 6, 2017 at 3:40 pm - Reply

    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?

    • Steinunn April 6, 2017 at 3:53 pm - Reply

      We figured this out!

      • Johann April 10, 2017 at 5:04 am - Reply

        Hi Steinunn,

        Glad you figured it out! 😉

  10. Swati July 6, 2017 at 5:33 am - Reply

    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

    • Johann July 10, 2017 at 5:30 am - Reply

      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.

  11. Niko July 21, 2017 at 7:21 am - Reply

    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

Leave A Comment