Power BI Intermediate Level: 48 - Creating a Custom Calendar Table for Time Intelligence

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You can create a custom calendar table which will allow you to plot data over different date granularities (e.g. year – quarter - month name), enable you to plot different measures over a single time axis, and enable you to use time intelligence functions.

Automatic Hidden Calendar Table

When you plot a measure over a date column, Power BI's default behavior is to generate a date hierarchy from that date column and in the visual you can change the date granularity between Year, Quarter, Month, and Day.

Technically, what happens is that Power BI generates a hidden date table for each date or datetime column which you use in a visual. You cannot view those hidden tables in Power BI but you can view them using the third-party open-source tool DAX Studio. DAX Studio is quite useful for viewing and exporting data from a Power BI data model.

Each automatically generated calendar tables has a continuous range of dates from the earliest to the latest date from the column of interest, and contains additional columns for Year, Month, etc.

If you don't like the behavior of automatically generating these tables, you can turn off the functionality in the Power BI options for the current file, under Data Load, Time intelligence.

Advantages of Using a Custom Calendar Table

If you want to get serious about using time intelligence in Power BI, you should create a dedicated calendar table. I will show you how to do it in Power Query, however you could alternatively do it in DAX using the CALENDARAUTO function. The reason I prefer to do it in Power Query is because I find it to be much more flexible. Here are the main advantages of using a dedicated calendar table:

• You only need a single calendar table for your whole report, making it easier to maintain
• You can use additional columns for filtering or usage on the time axis, for example calendar weeks, name of weekdays, etc.
• You can plot measures based on different tables over a single, shared time axis
• You can use time intelligence functions which require a continuous date range
• You can turn off the automatic creation of hidden calendar tables and therefore reduce the model size. In some cases, the model size can be reduced by half

Creating a Custom Calendar Table

A custom calendar table has the following requirements to work properly:

• Contiguous and unique dates, meaning that from a start date to an end date, we have each date exactly once
• Mark the table in the Power BI data view as a date table

So let us create the calendar table in Power Query. It requires a bit of coding so I prepared the Power Query code for you. Start off creating a blank query.

Open the advanced editor.

Then, copy and paste the following code and confirm:

let
??? start_date = #date(2020, 1, 1),
??? end_date = Date.From(DateTime.FixedLocalNow()),
??? date_list = {Number.From(start_date) .. Number.From(end_date)},
??? #"Date table" = Table.FromList(date_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
??? #"Changed type to date" = Table.TransformColumnTypes(#"Date table",{{"Column1", type date}}),
??? #"Renamed Date" = Table.RenameColumns(#"Changed type to date",{{"Column1", "Date"}}),
??? #"Inserted Year" = Table.AddColumn(#"Renamed Date", "Year", each Date.Year([Date]), Int64.Type),
??? #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), Int64.Type),
??? #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
??? #"Inserted Days in Month" = Table.AddColumn(#"Inserted Month Name", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
??? #"Inserted Quarter" = Table.AddColumn(#"Inserted Days in Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
??? #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Calendar Week", each Date.WeekOfYear([Date]), Int64.Type),
??? #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
??? #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
??? #"Inserted Day Name"?        

Now you have your own custom calendar as shown below.

Finally, we need to mark the calendar as a date table in the Power BI table view as shown below.

While we are there, let's sort the Month Name column by the Month Number column to get the correct order of month names in visuals: January, February, March, etc.

Additional Information

This specific calendar reaches from January 1, 2020 to today, with today updated whenever you refresh the query. You can change the start and end dates by clicking on the first or second step and changing the value in the formula bar. If you can’t see the formula bar, go to the View ribbon and enable it there.

I used the start and end dates to generate a contiguous list of dates, then converted that list into a table. After that, I added new columns based on the Date column, as shown below.

Feel free to add more columns as needed. You can use my calendar in any way you want and modify it to your needs.

Note that some businesses use special calendars, such as the 4-4-5 calendar often used in the retail industry. If you need such a special calendar for Power BI, you can easily find some appropriate Power Query code online.

Testing the Calendar Table

Let’s test our calendar table. Cerate a Matrix visual, drag the year, quarter, and month name on rows, drag the Date into values and change summarization to Count, and then on the visual click the Expand down button twice. We get the correct count of days and can use any date hierarchy which we like. Note that year 2020 was a leap year with 366 days.

Conclusion

Now you have your own custom calendar table, based on a date range which you choose and featuring additional columns to use such as calendar week and day of week. You can reuse the calendar table in all of your reports and easily in Power Query. With the custom calendar table in place, we can perform complex time intelligence.

In the next article, let's start putting the calendar table to good use by plotting data of two different tables over a shared time axis.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Plotting over Dates Using a Custom Calendar Table