Power BI Calendar Tables

Level: Beginners 

First posted May 2015, Updated Feb 2021.  Many aspects of this article also apply to Power Pivot for Excel.

Technically you don’t need calendar tables to use Power BI.  Power BI comes with an inbuilt date capability called “Auto Time Intelligence”.  But the inbuilt capability is very basic, and worse still, it has some negative side effects.  In this article I cover what comes out of the box and the preferred approach of building your own power BI calendar table.

Auto Time Intelligence

Auto time intelligence is the way Microsoft makes it easy to “roll-up” dates into the concepts of Months, Quarters, and Years.  This feature is on by default. You can find (and change) the setting by opening Power BI Desktop and navigating to File\Options and Settings\Options as shown below.  In the section Global\Data Load (shown below) you can turn this feature on/off permanently.

You can also navigate to Current File \Data Load and turn it on/off just for the active workbook (as shown below).

Auto time intelligence is very basic in its capabilities and it has a number of issues.

• It only works on calendar years (no fiscal years – unless you are fortunate that your fiscal year is the calendar year).
• It is always at the day level of granularity.
• It only rolls up from days to months, quarters and years.  There is no handling of weeks, or trimesters, or ISO (445) calendars, or any other business specific time periods that may be needed.
• It creates 1 hidden calendar table for every date column in your report.  This fact can make the size of your workbook grow much larger than it would otherwise.
• Each calendar table is independent; there is no way to model a single slicer to filter multiple date columns (although this can be done in the UI by grouping slicers).

This auto time intelligence is really aimed at absolute beginners who do not want to learn how to model their own data.  My view is that you should turn this feature off and instead build your own calendar table.

Reasons to use a Calendar Table in Power BI

There are many good reasons to create your own calendar table in Power BI.  Building your own calendar table allows you to:

• Use the filtering power of Power BI to make your reports fast and snappy by accessing relevant columns of data that form part of the calendar table.
• Filter your reports on attributes such as Year, Month, Quarter across as many data tables as you need (eg Actual Sales, Budget Sales, Stock on Hand) as well as any other aggregation of time you need for your business.
• Create your own custom view of time that may be different to a standard calendar (such as a 4/4/5 calendar) and/or create your own definition of what is a financial year.
• Create calendar tables for time periods that are not at the “day” level, eg Month, Year, Minute, etc.
• Build concepts like a time intelligence calendar table. These can be used to allow a user to select a time horizon from a slicer (e.g. Today, Yesterday, This Week, Last Year) and have the report automatically update to reflect that time period.

In some situations you simply must have a calendar table:

• If you want to use some of the more advanced inbuilt time intelligence functions in DAX (like rolling 13 week sales).
• Load and report on data that is not at the day level of granularity. 

If you load a “standard” calendar table in Power BI (rules below), you can use the inbuilt time intelligence functions. There are other ways to create time intelligence formulas other than the inbuilt functions, but generally the DAX is a bit more complex.  More on that later.

Rules for a Standard Power BI Calendar Table

Here are the rules to build a conforming standard calendar table.  You must follow these rules if you want to use time intelligence functions.  You don’t have to follow these rules if you don’t need/want to use the inbuilt time intelligence functions (but it is still good practice particularly if your data is at a “day level” of granularity).

• You must have a date column in your calendar table
• The dates in this column must be in a contiguous range that covers the entire period of your data from the first day of the first year of your data to the last day of the last year of your data:
  • No missing dates.  It doesn’t matter if you don’t work weekends, you MUST include ALL dates in the calendar table including weekends.
  • No duplicate dates
• The calendar table can be marked as “date table” (specifying the date column when prompted).
  
  • You must use this step if the calendar table is joined to a data table using a surrogate key (eg if your join is on a text column like DDMMYYYY, which technically is text, not a date).
  • You must use this step if you want to use quick measures to help you write time intelligence functions using your own calendar table.
  • Apart from the 2 scenarios above, this step is not mandatory for the calendar table to work.

Additional Features of a Good Calendar Table

In addition to the above mandatory features (for inbuilt time intelligence to work), there are some things that you should do to get the most out of any calendar table you build.

• Include columns for every time attribute you want to use in your reporting. eg Year, Month, Week, Day Name of Week etc.
• Include an integer ID column that starts at 1 and increments by 1 for every relevant column in the calendar.  E.g. create a MonthID column that starts at 1 and increments by 1 for every month without resetting at the end of each year (1,2,3,…12,13,14 etc). This is very useful for writing custom DAX time intelligence formulas.
• Include a numeric column for every alpha column that needs to be sorted in a specific order.  This is because Power BI columns always sort in alpha numeric order.  So the month column will sort in the order Apr, Aug, Dec, Feb instead of Jan, Feb, Mar, Apr.  You need to include a numeric month column so you can tell Power BI to sort the alpha month column in a different order.  Use the “sort by column” button inside Power BI to change the default sort order of the alpha column.  Also note that the numeric sort column should have a 1 to 1 relationship to the alpha column.  i.e. there should be 1 and only 1 value in the numeric column for each value in the alpha column. (note, this is the easiest way to deploy, however note that technically it doesn’t have to be 1 to 1.  Read about that in Daniil’s blog here )

How to Connect Data Tables to a Calendar Table

Load your calendar table into the Power BI data model.  You then need to join your data table(s) to the calendar table using the date column from both tables.  It is also possible to join your data table to the calendar table using a date key column (surrogate key) instead of a date column. This is most common when your calendar table is a different level of granularity than “day”, eg week, month etc.

Below I have joined a data table to the calendar table using the date columns in both.

How to Create a Power BI Calendar Table

There are several ways to create a calendar table for Power BI.

• Load it from a data warehouse (if you have one)
• Build one in Excel
• Build one in Power Query
• Use DAX table functions

Excel Calendar Tables

I think that Excel is the most flexible because you can hard code any value you need. This can be good if you have variable business rules as to when your year starts (eg for 445 calendars).  Just create a blank worksheet and start adding the columns you need.  You can use formulas in your columns to calculate the values for Year, Month etc.  Eg you can use =YEAR([Date]) to create a year column from your date column, although this generally only works for regular calendars (not 4/4/5 calendars etc).  Just hard code the values that don’t follow any formula logic.

Import the data from your Excel workbook into every Power BI workbook where you need a calendar table.

Power Query Calendar Tables

My preferred approach to building a calendar table is to use Power Query. Once you write the query it is “set and forget”. The table will automatically grow as time progresses (not something that will happen with Excel).  I have a separate blog article on how to do this in Power Query.

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

If you want to learn Power Query, checkout the details of the Power Query Online training course.

DAX Table functions

While it is possible to build a calendar table using DAX functions, I do not recommend this approach. I believe in using Power Query to prepare the data where possible, hence that is my recommendation for most use cases.  You can use the SQLBI calendar table template if you want to use DAX.  But be warned, the DAX is 1,500 lines of code.

How to use a Week or Month Calendar

So far I have only talked about daily calendars.  If you want to use the inbuilt time intelligence functions then you must use a daily calendar as described above.  However if your data is at a weekly or monthly level of granularity and you don’t want/need to use the inbuilt time intelligence functions, then you can use a week or month calendar instead.  The difference is that the in-built time intelligence functions won’t work. Note you can still create your own custom time intelligence formulas but the DAX is generally harder to write (intermediate level DAX).

E.g. Instead of this

Total Sales FYTD =
     TOTALYTD([Total Sales], 'Calendar'[Date], "30/6")

You would need to write something like this

Total Sales FYTD =
     CALCULATE(
          [Total Sales],
          FILTER(ALL('Calendar'),
                 'Calendar'[FinYear] = MAX('Calendar'[FinYear]) &&
                 'Calendar'[FinWeek] <= MAX('Calendar'[FinWeek])
          )
     )

I have a comprehensive blog article about time intelligence here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

In addition, in a weekly or monthly calendar, you generally don’t use a date column but instead create some other unique time stamp ID to join the tables.  e.g., you can create a column like YYYYWW as the key (2101 for Week 1 2021, 2102 for Week 2 2021 etc).  Just make sure the same logic is used in your calendar table and also in your data table(s).  Same applies if you want to use months as your data granularity.  This is a snippet of a typical weekly calendar that would use the FinYearWeek column as the key to join to the sales table.

Note that it is important to use YYYYMM and not MMYYYY for a key column.  YYYYMM will naturally sort in chronological order where as MMYYYY will not.