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!