Excel Unplugged

Dynamic Calendar with Power Query or Power BI – Take 2

I guess every data professional that has ever taken on the challenge of creating data models either in Power Pivot or in Power BI knows the importance of the Calendar table. It gives you the ability to use Time Intelligence functions in DAX like DATESYTD, DATEADD, DATESINPERIOD…

Back in November of 2015 I wrote a post on how to create a dynamic Calendar based on your data. Now with the arrival of functions like CALENDARAUTO in Power BI or the Date Table/New in Power Pivot,

Dynamic-Calendar-with-Power-Query-or-Power-BI

the art of creating you own Calendar kind of faded away but it’s still the only viable option if you want to create a custom Calendar, so here is a quicker method than the one from November 2015.

First, just to refresh our memory and to know where this train is going, a Calendar table must have a column with Dates, that has all the dates that our data may include, but also all the dates between the earliest and the latest dates in our data. And there’s another small fact, each date must be unique. In other words, the Dates column must contain no duplicates!

Ok let’s go. We start with Power Query editor and a column that has dates

Dynamic-Calendar-with-Power-Query-or-Power-BI

Looking at the dates, you will notice that they occur somewhere between 2010 and 2022. Now lets create a Calendar from this column. First we change the data type to Whole Number. The easiest way to achieve this is to click the Calendar Icon and choose Whole Number from the Data Types available.

Dynamic-Calendar-with-Power-Query-or-Power-BI

This step, or at least the numbers themselves will not surprise anyone who knows anything about dates in Excel, but it might surprise someone that while we are aiming at creating a Calendar, we just got “rid” of all the dates. Seems kind of conflicting. But the reason we did it is the fact that it’s far easier to work with number lists than it is to work with date lists. Ok now we just rename the last step into something memorable and simple. So, right click on the last step and Rename.

Dynamic-Calendar-with-Power-Query-or-Power-BI

I named it Integers.

And now the magic part. We click the fx icon in the formula bar and write the following line:

= {List.Min(Integers[Date])..List.Max(Integers[Date])}

Dynamic-Calendar-with-Power-Query-or-Power-BI

What you’re basically writing is {1..10} which would return a list of numbers from 1 to 10 but with the smallest number and the largest number obtained by the Min and Max functions. So simple really 😊

Two important things, Integers is the name of the renamed step and Date is the name of the column containing dates.

And there it is, a list of all the numbers with only two steps left to turn it into a Calendar. First we turn the list back to a table. We do that with the Transform/To Table command

Dynamic-Calendar-with-Power-Query-or-Power-BI

And then we simply change the data type back to Date

Dynamic-Calendar-with-Power-Query-or-Power-BI

And Voila

Dynamic-Calendar-with-Power-Query-or-Power-BI

Our Date column of the Calendar done. So simple it should be illegal 😊

Comments 2

  1. edh says:

    Don’t you really have to go one step further so it the calendar goes to the beginning and end of the first/last years so any of the date logic in Power Pivot or Power BI (the YTD/MTD/QTD type formulas) have the full range of dates to work with? My understanding is those won’t work if you have partial years of dates.

    I do like how you use the list though to get started. I’d have to play with it but if you wrapped the first {List.Min(Integers[Date])… as
    {#date(Date.Year(List.Min(Integers[Date])),1,1)…

    That should give you Jan 1 of the first year in the list. But I am not 100% sure that would work with out testing it, and that doesn’t work for those in non-calendar years, not the 1,1 anyway.

    1. It’s not really necessary it’s just good practise, but sure, that would be even better… I would maybe go for a duplicate column then Transform/Date/Start of Year on one and so on…

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: