PowerPivot rollups of the previous slicer period using DAX

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
create table [dbo].DIMYear
--id Identity ,
[CurrentYear] [char] (4)
,[PreviousYear] [char] (4)

Then I inserted the period entries:

INSERT INTO [DBName].[dbo].[DIMYear]

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!