Power Pivot Calendar Tables

Level: Beginners

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).image
  • 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.

image

L2WD banner ad

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.

image

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).

image

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.

Create a Custom Calendar in Power Query

Create a 445 Calendar using Power Query

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

You would need to write something like this

  • 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.

image

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

May 06, 2015 9:19 am

10 Comments

  • Matt…
    Nice post….
    Do you have any suggestions for keeping calendars in sync in multiple files? I’m finding I need more than one powerpivot file (likely will settle on 3) but need the same calendar in each. I tend to make additions to one calendar and then realize I need to update the other 2 files.
    Mike

  • The only way I know to do that is with the Excel approach mentioned above (but it would work with SQL Server etc too). You have 1 master workbook for your calendar and import it in each workbook where you need it. But it is hard to manage the over run issue this way. The trouble will always be if you add/delete a column, it won’t auto flow into each workbook. You will need to go to table properties and change the column settings to make it flow. But that is pretty easy.

    Currently the approach I take is to save my power query code in a blank workbook and save it as a template. When I need a new workbook with a calendar, I just use that template. If I forget (which I do) I have the code saved in One Note and I just cut and paste it into a blank query. If I need to change it, I change it in my master template and then update my copy in One Note. When I get to a workbook that needs updating, I just replace the Pq code at that time. Not ideal, but at least this way your calendar is getting incrementally better each time. At some point you will have nailed it and won’t need any more changes. If you don’t have everything I mentioned above, I recommend you add these things. You can always filter out columns you don’t need, or just leave them in – they won’t hurt.

    1. Interesting approach that I need to study…the more I read about powerquery the more I think I need to take a bunch of time and learn it like I have powerpivot.

      Right now I’m trying to reduce the number of files to reduce duplication of tables such as the calendar but I’m getting to the point where some of the files are starting to show performance issues.

      Thanks for the quick repsonse!
      Mike

  • Hi Matt, when using monthly granularity for the date table, how would I calculate the annual total of an expression for a year selected on a slicer regardless of the month selected on a different slicer?

    I’m having trouble understanding the difference between these trial and error attempts and why they invariably end in error:

    =CALCULATE([Measure],FILTER(Calendar, all(Calendar[Month of year]);
    =CALCULATE([Measure],FILTER(Calendar, allselected(Calendar[Year]);
    =CALCULATE([Measure],FILTER(all(Calendar), allselected(Calendar[Year]);
    =CALCULATE([Measure],FILTER(Facttable, all(Calendar[Month of year]);

    And other permutations of the above.. some give me incorrect results and others are nonperformant to the point of forcing me to reach for the Esc key.

    What am I missing here?

  • Hi Andre

    ALLSELECTED will respect the initial filter context on slicers and filters (ignoring rows and columns). So if you have 2 slicers (one for year, one for month) it will respect both. The process to solve these problems is to think in “English” (not DAX) about the steps to solve the problem (while thinking like the power pivot engine), then converting the solution to DAX.

    In English: you want to remove all current filters from the initial filter context and then reapply a new filter only to the current selected year. So how about harvesting the year selected using an aggregator and then re-apply a filter to that year only. Something like this.

    =CALCULATE([measure] , FILTER(ALL(Calendar) , calendar[year]=max(Calendar[Year]) ) )

    1. Thank you Matt, that works a treat. I extended the concept to an AVERAGEX function with great success (bye-bye calculated column chewing up all my memory!)

  • Hi Matt:
    I have followed the steps from various online sources to create a calendar file. I created a relationship for the calendar table to the functional table. When I drag a calendar field (month or quarter) in to columns or rows, they appear as expected. When I pull data from the other table, the data fields disappear. I have researched for the last few days and checked formatting for the joined date fields. Nothing seems to be working. The date field on my data table does include time values. I suspect this is the culprit based on articles and videos I’ve research, but I can’t find the right solution to correct the problem. Can you help?

    1. Yes, the time stamps would cause this problem. You need to strip the timestamps off your data. The best way is to do this on data load. Can you load the data from Power Query? If so, you can convert from date/time to date prior to load. This is the best approach. If this is not an option, you could add a new calculated column in your data table and write a formula to display just the data portion. This is very inefficient and I don’t recommend it, but at least you could test that this solves the problem. I am sure it will.

  • Hi Matt,

    I was working on one report and couldn’t actually complete it on PowerBI due to my limited knowledge. I have been asked to created a bar graph that shows previous years performance on right side like one bar for 2014, one for 2015 and then current years months and at the end it should show me YTD. Is it possible to do it on powerbi

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

Bring your data to life - Let's discuss how Matt can help!