Technically you don’t need a calendar table to use Power Pivot, however in all but the most basic use cases you really should use one. Reasons to use a Power Pivot calendar table include:
- A calendar table allows you to use the filtering power of Power Pivot to make your reports fast and snappy.
- It allows you to filter your reports on attributes such as Year, Month, Quarter and any other aggregation of time you like.
- It allows you to create your own customer 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.
And indeed in some situations you simply must have a calendar table – specifically if you want to use the inbuilt time intelligence functions in Power Pivot (like year to date measures, same period prior year etc). Now there are other ways to create formulae that calculate time intelligence results other than the inbuilt functions, but generally the DAX is a bit more complex. More on that later.
Rules for a Calendar Table
Here are the mandatory rules of a date table 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 in built 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:
- 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 must be marked as “date table” specifying the date column when asked in the dialogue box if you are using a non-date column. (This step is not required in Power BI as you can’t use a non-date key, only Excel)
- The date calendar reportedly needs to run through to the end of the last year used in the data model (although in my experience this makes no difference).
Note: It is not mandatory for your calendar table to be at the “day” level of granularity it could be week, month, hour, minute or what every else you want but you can’t use the inbuilt time intelligence functions unless it is at the “day” level.
Additional Features of a Good Calendar Table
In addition to the above mandatory features, there are some things that you should do to get the most out of your calendar table.
- Include columns for every time attribute you want to use in your reporting. eg Year, Month, Day Name of Week etc
- Include an integer ID column that starts at 1 and increments by 1 for every entry in the calendar (in chronological order). This is very useful for writing custom DAX formula.
- Include a numeric column for every alpha column that needs to be sorted in a specific order. This is because Power Pivot 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 Pivot to sort the alpha month column in a different order. Use the “sort by column” button inside Power Pivot to change the default sort order of the alpha column. Also note that the numeric sort column must have a 1 to 1 relationship to the alpha column. ie there must be 1 and only 1 value in the numeric column for each value in the alpha column.
How to Connect Data Tables to a Calendar Table
Load your calendar table into the Power Pivot data model. You then need to join your data table 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 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 Calendar Table
The easiest way to create a calendar table is to use Excel. Just create a blank worksheet and start adding the columns you need. You can use formulae 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).
Also note that you will generally be better off to create a new workbook for your calendar and save it somewhere you can easily find it later. Import the data from your Excel workbook into every Power Pivot workbook where you need a calendar table. This has 2 benefits. Firstly you only have to create the date table once (use many times) and secondly it is best practice to not use linked tables in your workbooks (there are exceptions). If you use a linked table, the data is first stored uncompressed in your workbook, and then stored again in a compressed format in your data model. The uncompressed copy will typically take up 6-10 times as much space as the compressed version and hence it is an overhead that you don’t need and can be avoided if you import from an external dedicated workbook.
An alternative approach to Excel is to use Power Query.
One problem with using Excel to create your calendar table is “date over run”. Some DAX measures like YTD have a problem where the data in your data table will end before the dates in your calendar table end. This causes a problem in some reports/pivot tables and this problem then needs to be resolved using more complex DAX. The easiest way to solve date over run is to only load dates in your calendar table up to and including the last date in your data table.
This is where Power Query comes in. Power Query is not only a great tool for creating calendar tables, but it is also very easy to solve the date over run problem. I have written a number of blogs on how to use Power Query to create calendar tables and these blog posts include detailed instructions on how to do it. Here is the most recent blog article.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/
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 or 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 differences are
- In built time intelligence functions wont work. Note you can still create your own custom time intelligence formulae but the DAX is generally harder to write (intermediate level DAX).
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]) ) )
- 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. eg you can create a column like YYWW as the key (1501 for Week 1 2015, 1502 for Week 2 2015 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 YYMM and not MMYY for a key column. YYMM will naturally sort in chronological order where as MMYY will not.
And that’s it. Let me know what type of issues you have had with calendar tables and if these recommendations solve those problems.
If you would like a deep dive into the more technical side of calendar tables, you can read this post here http://mdxdax.blogspot.com.au/2011/01/dax-time-intelligence-functions.html