Power Pivot Calendar Tables - Excelerator BI

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.

Power BI Online Training

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.


Power BI DAX Book


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/

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 =
          [Total Sales],
                 '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

21 thoughts on “Power Pivot Calendar Tables”

  1. Hi Matt,

    A little quirk I found playing around with a calender table. I created a table in Excel and “pasted” it into Power Pivot.

    The first 12 days of each month changed from “dd/mm/yy” (Aust setting) to “mm/dd/yy” (US setting). The remaining dates (13th to 30th etc) were fine.

    I did as you have explained and created a separate table in a separate file and all is fine.



  2. sangay choden

    Hi Matt, Its been so hard to customise week number based on dates , but I see you have done nicely in your sheet. ID for week which is customised. Using the function WEEKNUM gives the weeknumber starting from JAN till Dec which is not my purpose.

    Please show how to customise the week ID number

    1. The reason there is no standard option here is because there is not standard. I have this on my list of blog articles to write in the future. If you can email me a set of repeatable rules that tell me how your week number is calculated, I can take a look.

  3. Ruben van den Bovenkamp

    Hi Matt, I can’t seem to find a formula that works with Month ID (not even on the links you post to other blog).
    Can you help me in using MonthID to refer to last month? How does the dax know the ID of the current month?

  4. Hi Matt.

    How would I calculate previous month total sales if my date table is on a monthly granularity when using a date key? (ie YYYYMM format). I want to calculate the MoM change to compare previous months budget to this current month. I cannot simply use DATEADD functions as I cannot use the inbuilt time intelligence in this scenario.

    Any help would be appreciated here as I cannot seem to find a solution online anyway for my problem. I have tried this formula but it is returning an error:

    Total Budgets LM =
    [Total Budgets],
    FILTER (
    ALL ( ‘Calendar Creation Table'[DateKey] ),
    FILTER (
    VALUES ( ‘Date'[DateKey] ),
    ‘Date'[DateKey] – 01
    = EARLIER ( ‘Calendar Creation Table'[DateKey] )
    ALL ( ‘Date’ )

    1. You are close. Read my article here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

      Then, make sure you have a MonthID Integer column in you calendar table, starting at 1 and increasing by 1 for every month, forever. eg month 1 in the second year will be 13. Month 12 in the second year will be 24 etc. You then use this Month ID column in your formula (following the instructions I provide on the page linked above). Post back how you go.

      1. Hi Matt.
        Went extremely well.

        I now have the MTD figures for previous months, as well as 2 months ago. I struggled with this a lot at first, so thank you so much for your help and great article.

  5. Hi Matt,

    If my data table is reporting monthly granularity, do I need to have a calendar table that is using daily granularity? I can’t see how I would be able to create a relationship between the two if they’re not using the same granularity.

    1. You don’t need a day calendar if your data is monthly. Just create a yymm column in both to join the tables. Be aware that inbuilt time intelligence won’t work – you have to write your own custom time intelligence. Read my time intelligence in my knowledge base

  6. Hi Matt,

    I have created a calendar table in Power BI and I can sort by month. My data start from Jun 2016 to March 2017. The problem comes when the year increases – from 2016 to 2017. Then the date is displayed as 2017 months first and then 2016 months. i.e. Jan, Feb, Mar, Jun, Jul, Aug…….

    I tried creating a column YYYYMM and tried to index the month name using that column in DAX. But I get an error “.. You cannot sort the Month Name column by YearMonthIndex. You cannot have more than one value in YearMonthIndex for the same value in Month Name.”

    How can I sort the months so that the roll over of year will not cause a problem?

    Thanks in advance.

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

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

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

  10. 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?

  11. 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!

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

Leave a Comment

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

Scroll to Top