Calculating Moving Averages in PowerPivot / DAX

A common calculation required in business is moving averages. How can we create it in DAX?  Lets walk through the calculation.

Imagine we have the following PowerPivot table:

image

In order to calculate a three month moving average, we first need to be able to grasp a reference to the last two months and the current month – for each of the months listed on the table.  In other words, if I am in September of 2001, I should be able to grab July, August and September of 2001.    How do we do this in DAX?

Using DAX filter context you have the ability to make a reference to the in-memory date table, and – for each month in the pivot table – filter the table only for the current month and the last 2 prior months.  Lets see this in action:

Lets start by trying to grab a reference to the current month. To test it, we can use DAX’s VALUES function which returns a table with distinct values depending on the current context.  As we want to “grab” the current month, we try creating a measure using this expression:

=VALUES(DimDate[EnglishMonthName])

DimDate is the in-memory table in the PowerPivot window that contains all dates, and “EnglishMonthName” is a column on that table.  This is the column that has been placed on the pivot table.

The result we get is an error.  It says:

ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (5, 57) A table of multiple values was supplied where a single value was expected.

Hmm.   The error says there is a table with multiple values.  Clearly, each cell’s output should represent only one value.  But.. why do we have multiple values when there is only one month in context?   The reason is that there is a grand total row (not shown above).   In the case of the grand total, there ARE multiple month values.  The way to avoid that error is by ensuring only one distinct month value is in context.   Try this now:

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1, VALUES(DimDate[EnglishMonthName]))

image

Great. so now we have a reference to the current month.   Now lets try getting a reference to two months ago.   We can do this using the function PARALLELPERIOD. 

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1, PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH) )

We get an error again. As you notice, the PARALLELPERIOD function takes a date for its first parameter, not a month.  In the context of the month, there are multiple dates.  When using PARALLELPERIOD, we get an output of multiple dates and that cannot be represented in once cell unless we tell the function which date to use.  So lets try this now:

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1, FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)))

The result is what we expect:

image

So if we get the first date 2 months ago, shouldn’t we capture the three months by getting the last date of the current month? Yes! and one way to accomplish that is by using the following expression:

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1, LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH)))

image

Ok, so now we have the boundaries of dates we need.  In DAX, a filter context can add or restrict the current context based on the expression used.  Using CALCULATE we can test we are properly aggregating the months we need:

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
    CALCULATE(
        SUM( FactResellerSales[SalesAmount] ),
            DATESBETWEEN(
                DimDate[FullDateAlternateKey],
                FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)),
                LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
            )
    )
)

We use the DATESBETWEEN function to return a one column table starting two months ago and ending on the last date of the current month. DAX will then add all the sales amount data over that ‘calculated’ table. The result?

image

What?  The formula gives the exact same result as the regular “Sales Amount” measure? 

The reason is that the expression is evaluated on the current’s month context.  In order to force the evaluation to happen across months, we must force the filter context to evaluate all dates (not just the current month).  We do this by adding one more filter to the expression:

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
    CALCULATE(
        SUM( FactResellerSales[SalesAmount] ),
            DATESBETWEEN(
                DimDate[FullDateAlternateKey],
                FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)),
                LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
            ), ALL(DimDate)
    )
)

image

Great! so now we have a 3 month moving sum.  If you are still following me, all else we need to do is change the aggregation from sum to average.   Simple change, right?

=IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
    CALCULATE(
AVERAGE( FactResellerSales[SalesAmount] ),
            DATESBETWEEN(
                DimDate[FullDateAlternateKey],
                FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)),
                LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
            ), ALL(DimDate)
    )
)

The result, however , is not what we expect.

image

Why?  Each DAX calculation “activates” certain rows on the tables loaded into the PowerPivot window.  For example, for July of 2001 the reported sales amount is 489,329.  However, this is an aggregate number: the underlying data is at the date level.   When we execute a DAX average function, we ask the PowerPivot engine to compute the average on the active rows, that is, all relevant rows at the level of granularity of the fact table. The output of the average calculation is, then, the correct daily average: not what we want.

Instead, we can add the sales amount for the period and then divide by the distinct count of months in the current context. Here is the complete expression:

IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
    CALCULATE(
SUM( FactResellerSales[SalesAmount] ) /  COUNTROWS(  VALUES ( DimDate[EnglishMonthName] ) )  ,
            DATESBETWEEN(
                DimDate[FullDateAlternateKey],
                FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)),
                LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
            ), ALL(DimDate)
    )
)

That’s it!  The result of the final formula is displayed below.

image

Advertisements

8 Responses to Calculating Moving Averages in PowerPivot / DAX

  1. Pingback: SQLBI - Marco Russo

  2. James says:

    Thanks, explaining the the intentional errors was a huge help.

  3. Joshua says:

    Is there a way to use PARALLELPERIOD and/or DATEADD to get a rolling 3-month total for non-consecutive months? For example, let’s say customer XYZ has has for Oct., Nov., and December. If I wanted to get a standard 3 month rolling, I could click on December and assuming I wrote my DAX function using a couple of PARALLELPERIODs …+(…-1, month)+(…-2,month), get the 3 month rolling. However, let’s say that company ABC had data for August, November, and December. Is there way way I can write a formula to pull data from the last 3 months that have it? So if I click on December (and there’s only data for December/November/August), it will return the 3 month aggregation for those 3 months b/c they’re the previous 3 months with data. If I click on December and there’s no data (but there is for say only November, October, and August), it’ll pull for Nov./Oct./August. Any ideas?

    • javierguillen says:

      Hi Joshua,

      For non-consecutive months, you must generate an auto increment identifier so that you can properly grab the last 2 months (plust the current one) regardless of how many months have been skipped ‘in between’. You can either generate that identifier on the actual query (as shown in this post) or you can use DAX to do this. Alberto Ferrari posted a entry some time ago which details a similar approach: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/26/powerpivot-stocks-exchange-and-the-moving-average.aspx

      If you do not have daily data, as the approach above gives an example for, then use one approach is for example, you to import the string value “Jan, 2011” from your source and then change the data type to ‘Date’ in the PowerPivot window. After this you can then apply the technique of assigning unique IDs to each date (using the EARLIER() function as explained in the link above). Finally, you can grab the necessary in-memory rows to compute the rolling average.

      Javier

  4. RubenC says:

    Hi Javier!
    Very interesting web page you have!
    I’m new on PowerPivot and would like to practice this sample.
    Your post mentions FactResellerSales.
    I have found several download links from Microsoft for Contoso but they only have FactSales.
    Maybe they have moved download link. Do you have idea where I could download the file you are utilizing?
    Thank you in advance.

  5. RubenC says:

    It did work!!
    I downloaded database and I’m able to access tables from PowerPivot.

    In the Contoso there is tutorial to add relationship and format up to a “X” point.. etc etc.
    Do you have a post or link to follow tutorial in order to have PowerPivot ready for the next step on “AdsventureWorks” so I can practice this blog = Calculating Moving Averages in PowerPivot??

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

%d bloggers like this: