I’m not going to delve into the intricacies of what a date table is and when you need one here, as it has already been extensively covered by numerous blogs and help links.
Rather, this post is dedicated to pointing out Power Pivot’s automatic date table function, which I have only recently discovered. I was pretty excited about it, because the traditional methods for creating a date table are rather laborious.
By contrast, Power Pivot recently introduced a great new function, that is accessed via the “Design” tab of the Power Pivot Manage window. It looks innocuous, but the little star in the top left corner is the clue.
You can only use it when you already have another table loaded in your data model, but once you’ve done that, it’s as simple as clicking on “New”, and a date table of the current year is generated for you! The next step is to alter the time frame, which you can do by clicking on Date Table again, followed by “Update Range”, where you just specify the start and end dates you’re interested in:
From here you can add further calculated columns to delineate financial or other custom calendars, or delete some of the default ones that are not of interest to you. You’ll find that the “Month” column is already sorted by the “Month Number” column (something that you had to do yourself in the past).
Here are some other column calculations you may find useful.
I quite often want to evaluate quantities in a financial year context (1 July to 30 June the following year). The formula I use for financial year designation looks like this:
FinYear = SWITCH ( TRUE (), 'Calendar'[Month Number] <= 6, [Year] – 1 & “/” & RIGHT ( 'Calendar'[Year], 2 ), 'Calendar'[Year] & “/” & RIGHT ( 'Calendar'[Year], 2 ) + 1 )
When you place FinYear and Month on the Row labels of your pivot table however, you’ll find that the months are still sorted by calendar year, rather than by July through to June, as you might prefer.
To overcome this, you’ll have to duplicate the “Month” column (just create a new column FinMonth and set it equal to [Month]). This column needs to be sorted by FinMonth Number, where July is month 1 and June is month 12. The FinMonth Number column can be calculated as follows:
FinMonth Number = SWITCH ( TRUE (), [Month Number] >= 7, [Month Number] – 6, [Month Number] + 6 )
So
Placing FinMonth instead of Month on the pivot table will result in the months having the correct sort order.
Inbuilt time intelligence functions in Power Pivot do not lend themselves to custom calendars, nor do they work in the context of Financial years. The good news is that it is entirely possible to write time intelligent functions for custom calendars, they’re just a bit more verbose.
The key to writing time intelligent formulas is to have an index for the custom column:
FinYearIndex = CALCULATE ( DISTINCTCOUNT ( 'Calendar'[FinYear] ), FILTER ( 'Calendar', [Date] <= EARLIER ( 'Calendar'[Date] ) ) )
Once you have that, the GFITW is your friend.