PowerPivot rollups of the previous slicer period using DAX

Keyur Nathani

December 10, 2013

All Post

SQL Data Tools is great in SQL 2012, but I had a challenge to show rollups as separate measures for the current slicer selected period, as well as a previous period.

First I created a CurrentYear/PreviousYear reference table for the slicer, and set up an active/inactive pair of relationships.

use DBName
go
create table [dbo].DIMYear
(
--id Identity ,
[CurrentYear] [char] (4)
,[PreviousYear] [char] (4)
)

Then I inserted the period entries:

INSERT INTO [DBName].[dbo].[DIMYear]
           ([CurrentYear]
           ,[PreviousYear])
     VALUES
           ('2009','2008'),
           ('2010','2009'),
           ('2011','2010'),
           ('2012','2011'),
           ('2013','2012')
GO

I added this to the Model, and built two relationships. An “Active” one for the Current Year, and an inactive relationship for the PreviousYear field.

To get the previous year, first I drop the filter using all(), then activate the dormant relationship to the previous year using the userelationship() function. Voila!

Here’s an example, works like a charm!

=CALCULATE(sum([CONTINUITY_CREDIT_SURPLUS_AMT_PROPORTION]),
all(CONTINUITY_CREDIT_DISTRIBUTION[CONTINUITY_CREDIT_WORKING_SET_YEAR]),
userelationship(CONTINUITY_CREDIT_DISTRIBUTION[CONTINUITY_CREDIT_WORKING_SET_YEAR],DIMYear