In this post I work through an example from Rob Collie’s book DAX Formulas for PowerPivot.
The problem to solve here is how to implement a “4/4/5” calendar calendar using DAX, i.e. one with 13 week quarters that are made up of fixed periods of 4, 4 and 5 weeks. The solution includes some key concepts in DAX and PowerPivot/Tabular models, including disconnected tables and a DAX construction that Rob Collie refers to as “The Greatest Formula in the World”(!).
The key requirement is that time intelligence calculations must adhere to these periods, e.g. when showing sales for a certain period or comparing sales between one period and another. So built-in DAX time intelligence functions such as PREVIOUSMONTH() or PREVIOUSQUARTER() that run off a column with date data type in a simple date table will not suffice, since the calendar months and quarters that they return will not map exactly to periods and quarters in the 4/4/5 calendar.
The first step is to create a source table that maps the 4/4/5 periods to real dates. So I created this table called Periods in the AdventureWorksDW2012 database in SQL Server:
I imported this into an SSAS tabular model, along with the standard AdventureWorksDW2012 FactInternetSales and DimDate tables:
The Periods table has no relationships defined with the other tables, and so is disconnected.
We can then create these 2 measures against the Periods table:
As they are measures they reflect the current context during the evaluation of DAX calculations, so the first returns the first date from the specified column for the current context and the second returns the last date from the specified column for the current context. When looked at in a Power BI matrix we see:
We can then create a measure that calculates sales for each period:
Sales in Period:=
FILTER(Date, ‘Date'[FullDateAlternateKey] >= [PeriodStartDate] &&
‘Date'[FullDateAlternateKey] <= [PeriodEndDate]) )
Example output for this is:
The [Total Sales] measure referenced in that expression is just defined by SUM([SalesAmount]), and is used to sum sales over all the dates returned by the FILTER() function. Specifically the FILTER() function identifies rows in the Date table that fall within the period start and end dates, and then the DateKey relationship from the Internet Sales table to the Date table allows the Internet Sales table to be filtered accordingly.
This next measure calculates prior period sales:
Prior Period Sales:=
CALCULATE([Sales in Period],
FILTER(ALL(Periods), Periods[PeriodID] = VALUES(Periods[PeriodID]) – 1)
as can be seen to the right here:
It’s the expression in the second argument of the IF() statement that Rob Collie refers to as The Greatest Formula in World (at least in the context of DAX custom calendars!). It does take some breaking down, not least due to the intricacies of how the FILTER() function works.
This first version just works on single periods, so the outer IF() statement excuses it from running on any higher period aggregations. CALCULATE() then takes the [Sales in Period] measure referenced previously and calculates it in a modified filter context. The first ALL(Periods) clears the filter context completely for the Periods table, for the CALCULATE() function. This makes sense since we’re not interested in the current period that’s in context – we want the previous one. The FILTER() function then sets the context to be the prior period.
This is the interesting part! Firstly there’s a second ALL(Periods). Why is that? It’s because FILTER() does not start out with an unfiltered table. The table that is its first argument reflects the current context of the pivot – not any modified context that may be going on inside the overall expression, such as the first ALL(Periods). So the second ALL(Periods) is clearing the slate for the FILTER() function in the same way that the first one does for the CALCULATE() function.
Then FILTER() does its filtering thing and returns the subset of rows for the unfiltered Periods table that have Periods[PeriodID] = VALUES(Periods[PeriodID]) – 1. VALUES() is guaranteed to return 1 row here because of the HASONEVALUE() guard in the IF() statement, which means its output will be implicitly converted to a scalar value such that 1 can be subtracted from it to get the prior period (this works because of the way PeriodID is implemented in the Periods table, i.e. increasing by 1 for each successive period).
The Periods[PeriodID] = VALUES(Periods[PeriodID]) – 1 part may look like it won’t work, since we want the Periods[PeriodID] on the right of the assignment to reflect the current Periods[PeriodID] filter in the pivot, but we’ve just done an ALL(Periods). But the second argument of FILTER() looks at the original pivot filter context – not the results of what’s just happened in the first argument.
The result of FILTER() within the CALCULATE() function subtracts from the current filter context. So the first ALL(Periods) leaves us with an unfiltered Periods table, and then FILTER() essentially subtracts rows from this, leaving only the subset returned by FILTER(), which is the prior period that we’re looking for, and which is then the sole period used by CALCULATE() to sum sales.
The formula above can be improved to work against prior periods at all levels, e.g. periods, quarters and years. The end result of this is in the rightmost column here:
and the formula that generated this is:
Prior Period Sales2:=
CALCULATE([Sales in Period],
Periods[PeriodID] >= MIN(Periods[PeriodID]) – COUNTROWS(Periods) &&
Periods[PeriodID] <= MAX(Periods[PeriodID]) - COUNTROWS(Periods) ) )
The COUNTROWS() function does the key maths here with regards how far back in time to look. If the current context just has one row filtered in the Periods table then the expression will just look back 1 period, else if it has 3 or 12 periods selected then the expression will look back 3 or 12 periods. The MIN() and MAX() functions here just find the first and last PeriodIDs for the current context, which are the starting places for each end of the current period range to subtract from.
Whilst the above example just looked at calculating prior periods, the same CALCULATE()/FILTER() structure shown above can be used to meet other time intelligence requirements on custom calendars, e.g. for year-to-date or year-over-year calculations. See Rob’s book for more details.