First published May 2015, updated February 2021. Many aspects of this article also apply to Power Pivot for Excel.
Technically, you don't need a calendar table to use Power BI. Power BI comes with a built-in date capability called "Automatic Time Intelligence." But the built-in capacity is very basic and worse, it has some negative side effects. In this article I cover what's out of the box and the preferred approach to building your own calendar table.
Automatic time intelligence
Automatic time intelligence is Microsoft's way of making it easy to "roll up" dates into the concepts of months, quarters, and years. This feature is enabled by default. You can find (and change) the settings by opening Power BI Desktop and navigating to File Options and Settings Options as shown below. In the Global Data Load section (shown below) you can permanently enable or disable this feature.
You can also navigate to Current File Data Load and turn it on / off for the active workbook only (as shown below).
Automatic time intelligence is very basic in its capabilities and has a number of problems.
- It only works in calendar years (not fiscal years, unless you are lucky enough that your fiscal year is the calendar year).
- It is always at the level of granularity of the day.
- It only accumulates from days to months, quarters and years. No weeks, quarters, ISO (445) calendars or any other company-specific time period that may be required are handled.
- Create 1 hidden calendar table for each date column in your report. This fact can cause the size of your book to 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 automatic time intelligence is really aimed at absolute beginners who don't want to learn how to model their own data. My opinion is that you should disable this feature and create your own calendar table instead.
Reasons to use a Power BI calendar table
There are many good reasons to create your own calendar table. Building your own calendar table allows you to:
- Use the filtering power of Power BI to make your reports fast and agile by accessing the relevant data columns that are part of the calendar table.
- Filter your reports by attributes such as Year, Month, Quarter in as many data tables as you need (for example, Actual Sales, Budgeted Sales, Available Stock), as well as any other time aggregation you need for your business.
- create your own custom view of time that may be different than a standard calendar (such as a 4/4/5 calendar) and / or create your own definition of what a financial year is.
- create calendar tables for time periods that are not at the "day" level, for example, month, year, minute, and so on.
- Build concepts like a time intelligence calendar table. They can be used to allow a user to select a time horizon of a segment (for example, 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 built-in time intelligence features in DAX (like rolling 13-week sales).
- upload and report on data that is not at the level of granularity of the day.
If you load a "standard" calendar table (rules below), you can use the built-in time intelligence functions. There are other ways to create time intelligence formulas in addition to the built-in functions, but in general, the DAX is a bit more complex. More on that later.
Rules for a standard calendar table
Here are the rules for building 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 built-in time intelligence features (but still good practice, especially 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 on 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 should use this step if the calendar table is joined to a data table using a surrogate key (for example, if your join is in a text column like DDMMYYYY, which is technically text, not a date).
- You should 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 it's not mandatory for the calendar table to work.
Additional features of a good calendar table
In addition to the mandatory features above (for the built-in time intelligence to work), there are a few things you need to do to get the most out of any calendar table you create.
- Include columns for each time attribute that you want to use in your reports. for example, year, month, week, day, week name, etc.
- Include an integer ID column starting at 1 and incrementing by 1 for each relevant column in the calendar. For example, create a MonthID column that starts at 1 and increases by 1 for each month without restarting 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 each alpha column that needs to be sorted in a specific order. This is because the Power BI columns are always sorted in alphanumeric order. Therefore, the column for the month will be sorted in April, August, December, February instead of January, February, March, and April. You must include a numeric month column before you can tell Power BI to sort the alpha month column in a different order. Use the "sort by column" button within Power BI to change the default sort order of the alpha column. Also note that the numeric rank column must have a 1-to-1 relationship with the alpha column. that is, there must be 1 and only 1 value in the numeric column for each value in the alpha column. (note that this is the easiest way to implement, however keep in mind that technically it doesn't have to be 1 to 1. Read about it on Daniil's blog Here )
How to connect data tables to a calendar table
Load your calendar table into the Power BI data model. Then you need to join your data tables to calendar table using date column from both tables. It is also possible to join your data table to calendar table using a date key column (surrogate key) instead of a date column. This is more common when your calendar table has a level of granularity other than "day", for example week, month, and so on.
Below I have joined a data table to the calendar table using the date columns in both.
How to create a calendar table
There are several ways to create a calendar table for Power BI.
- Load it from a datastore (if you have one)
- Build one in Excel
- Create one in Power Query
- Use DAX table functions
Excel calendar tables
I think Excel is the most flexible because you can hard-code whatever value you need. This can be good if you have variable business rules as to when your year starts (for example, 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, and so on. For example, 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 your Excel workbook data into every Power BI workbook where you need a calendar table.
Power Query calendar tables
My preferred approach to creating a calendar table is to use Power Query. Once you type the query, it is "set and forget". The table will grow automatically as time goes by (not something that happens with Excel). I have a separate blog post on how to do this in Power Query.
For a complete lesson on how to use Power Query, check out the training course details by clicking the image below.
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 data whenever possible, so that's my recommendation for most use cases. You can use the SQLBI calendar table template if you want to use DAX. But be careful, the DAX has 1,500 lines of code.
How to use a weekly or monthly calendar
So far I have only talked about daily calendars. If you want to use the built-in time intelligence functions, 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 built-in time intelligence features, then you can use a weekly or monthly calendar. The difference is that the built-in time intelligence functions will not work. Note that you can still create your own custom time intelligence formulas, but the DAX is generally more difficult to write (intermediate-level DAX).
For example, 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 full blog post on weather intelligence here https://exceleratorbi.com.au/dax-time-intelligence-beginners/
Also, on a weekly or monthly calendar, you generally don't use a date column, but instead create some other unique timestamp ID to join the tables. for example, you can create a column like YYYYWW as the key (2101 for Week 1 of 2021, 2102 for Week 2 of 2021, etc.). Just make sure the same logic is used in your calendar table and also in your data table (s). The same applies if you want to use months as the granularity of your data. This is a snippet from a typical weekly calendar that would use the FinYearWeek column as a key to join the sales table.
Note that it is important to use YYYYMM and not MMYYYY for a key column. YYYYMM will naturally be sorted in chronological order, while MMYYYY will not.