I guess every data professional that has ever taken on the challenge of creating a data model 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,
the art of creating you own Calendar table for your data model 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
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.
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.
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])}
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
And then we simply change the data type back to Date
And Voila
Our Date column of the Calendar done. So simple it should be illegal ????
We’re continuing our quest for beautiful and dynamic ...
Let’s look at some crucial tools for creating polished ...