Simple time series calculations, such as comparing sales in the current year with the previous year, are easy to do in DAX and well-documented. What happens if, in PowerPivot or the SSAS 2012 Tabular model, you want to compare the sales in any two arbitrary time periods – for example you want to compare (and maybe find the growth from) sales in January last year and February this year?

There are several ways of solving this, but here’s one. First, you need two Date dimension tables, one for each of the two time periods you’re comparing. Using the Adventure Works DW database here’s an example of how this can be done:

In this case I loaded DimDate and FactInternetSales and created an active relationship between the two tables on OrderDateKey=DateKey. I then loaded DimDate again, called it DimDateCompare, and created another relationship between it and FactInternetSales on OrderDateKey=DateKey but set this second relationship as inactive. This means that any selection on DimDateCompare will be ignored unless the relationship between it and FactInternetSales is activated using the UseRelationship() function.

You can then create a measure to get the Sum of the SalesAmount column for the selection on DimDate, which is simply:

Sum of Sales Amount:=SUM([SalesAmount])

Then create another measure that uses the relationship between DimDateCompare and FactInternetSales and ignores any selections on DimDate (to do this, you just need to use the All() function):

Sum of Sales Amount for Comparison Period:=

CALCULATE(

SUM([SalesAmount])

, ALL(DimDate)

, USERELATIONSHIP(FactInternetSales[OrderDateKey], DimDateCompare[DateKey]))

And finally create a measure to find the difference between these two measures:

Difference:=[Sum of Sales Amount] – [Sum of Sales Amount for Comparison Period]

To show how this works, look at the following PivotTable showing these three measures:

I have CalendarYear from DimDate on the rows of the PivotTable and CalendarYear from DimDateCompare on the Slicer. The Sum of Sales Amount measure shows, as you would expect, the sum of sales for each year on DimDate; the Sum of Sales Amount for Comparison Period ignores what’s on rows and returns the sum of sales for the two years selected on DimDateComparison, ie 2001 and 2002. This is £3,266,373.66 + £6,530,343.53 = £9,796,717.18; and Difference shows the second of these values subtracted from the first.

Not a very interesting calculation in itself perhaps, but I’m intrigued by the possibilities of having tables in the model that only have inactive relationships with other tables; up to now I’ve only used inactive relationships in scenarios where there have been another active relationship between the same two tables, for example as described in Kasper’s post here.

Just a note for big Tabular databases (not for PowerPivot I think) – using inactive relationships seems slower than using active ones. I’m not sure about the reason, but this is something I experienced on database of several Gb in size (some hundreds million of rows in the largest table).

to avoid inactive relationships in this case you could simply add

ALL(DimDateCompare) to the first calculated measure “Sum of Sales Amount” and define the relationship as active between facts and DimDateCompare.

Yes, but then you’d need to add that to all your measures which would be a pain. Using an inactive relationship means it will only affect measures when you want it to.

This is amazing! I’m a PowerPivot newbie and this just reduced my file size by 50% and queries are much quicker. My data table is more than 15,000,000 rows and I was DUPLICATING this table just so my pivot tables could contain “Current” and “Prior” data columns.

This exercise in ‘time series’ now allows the use of two slicers on one data set to pull any date range I want. Simply brilliant!

Just for my curiosity: why you have 15 million rows in a date table? Do you include also the time and not only the date?

He has 15 million rows in his data table, not his date table

Thanks Chris – I was misreading it because of the need of duplicating data…

Hi Chris,

Thanks for this wonderful post!!

Can you please help me with below problem?

Scenario: I wanted to get the last 12 weeks data into my report.

VALUE (

CONCATENATE (

YEAR ( TODAY () ),

FORMAT ( WEEKNUM ( TODAY () ), “00” )

)

)

– 11

the value function is returning “201503” however the subtraction with 11 i am getting “201492”. which is applying wrong filters on my data..

Will you plese help me in finding the 12th week back from today?

Thank you very much!!

Regards,

Maddy

You should create a column in your Date table that has an incremental value for each week, regardless of the year. You can find a couple of examples in http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ and http://www.daxpatterns.com/time-patterns/

Hi Marco,

Thanks for your prompt reply..

Suppose say in my report i have a RollbackWeeks drop down which contains 12,24,36,etc.

When a user selects 12, i wanted to fetch data for last 12 weeks. if user selects 24, i wanted to fetch data for last 24 weeks.

If i create a new column or measure in dax that will do a roll back on workweek, is there a way i can pass the user selected value dynamically?

Thanks,

Madhuri

Yes, look at this article: http://www.daxpatterns.com/parameter-table/

I am wondering if you have any ideas for getting the granular details for quarters or months? When I use your formula, I get the value for the sum of the whole year at the month level …when in reality I want the sum for the month only. I want to compare not only at the year level of detail but also down to the month level.

I figured it out, I changed the ALL filter so that it referenced the column for the year instead of the whole DimDate table. There may be another way to do this but it worked in my situation.