Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Comparing Any Two Time Periods in DAX

with 7 comments

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:

image

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:

image

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.

Written by Chris Webb

June 20, 2012 at 2:48 pm

Posted in DAX, PowerPivot, Tabular

7 Responses

Subscribe to comments with RSS.

  1. 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).

    Marco Russo

    June 20, 2012 at 8:13 pm

  2. 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.

    gbrueckl

    June 21, 2012 at 9:31 am

    • 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.

      Chris Webb

      June 21, 2012 at 10:26 am

  3. 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!

    Matthew K

    February 18, 2014 at 7:46 pm

    • 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?

      Marco Russo

      February 19, 2014 at 4:41 am

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

        Chris Webb

        February 19, 2014 at 8:49 am

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

        Marco Russo

        February 19, 2014 at 9:38 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers

%d bloggers like this: