Moving Averages, Sums, Etc.

 
Moving Average in Power Pivot

The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend

I realized recently that this topic has never been covered before, in its most straightforward form, on this site!  Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).

To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model:

 

Simple Power Pivot Model

And a simple pivot:

image

That [Units Sold] measure is the jagged red line in the chart at the top of the post, and its formula is very simple:

[Units Sold] = SUM(Sales[QtySold])

And we want a version of [Units Sold] that is “smoothed” over a 3-month period.

Moving Sum

Let’s start with a formula that is a sum of the most recent 3 months (including the current one):

[3 Month Moving Sum Units Sold] =
CALCULATE([Units Sold],
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3, Month
                       )
         )

And see what that looks like:

Power Pivot Moving Sum

Moving 3-Month Sum Reflects the Current Month and the Prior Two Months

Moving Average – First Attempt

OK, but that number is bigger than a single month and doesn’t match the scale of our real-world business, so we wouldn’t want to chart that – we want the average version of that.

It’s a 3-month moving sum, so to get the average, we could just divide by 3:

[3 Month Avg Divide 3] =
=[3 Month Moving Sum Units Sold] / 3

Which looks like:

Power Pivot Moving Average with a Drawback

3 Month Moving Avg Via Divide by 3 Has a Drawback

Those first two months, since they are the first two months in our calendar, are summing up less than 3 months’ worth of sales, but still dividing by 3.  So it “unfairly” drives down their average.

Moving Average – Corrected

We can account for this by changing our denominator to use a similar logic to the numerator:

[3 Month Moving Avg Corrected]=

[3 Month Moving Sum Units Sold] /
CALCULATE(DISTINCTCOUNT(Calendar[Year Month]),
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3,Month
                       )
         )

In English:  “take the 3 month sum measure we already have and divide it by the number of distinct (unique) months we have over that same 3 month period.”

Results:

Power Pivot Moving Average Fixed

This Calc is More “Fair” to the Months at the Beginning

Variations?

There are a number of variations on this approach – daily/weekly/quarterly versions, correcting for calendars that extend beyond the range of dates where you have sales, adapting it to custom calendars via the Greatest Formula in the World, etc., but I will wait and see what people ask about in the comments before digging into any of those.

10 Responses to Moving Averages, Sums, Etc.

  1. Ola says:

    Is it complicated to control “X Months moving” from Excel?
    Have a cell in Excel where we can put 3, 6, 12 or even 1.

    • powerpivotpro says:

      We can do that with a slicer but not a cell. Is that sufficient?

      • nexusstone says:

        Having a slicer to easily/quickly (re)set the moving average length would GREATLY enhance the ability to see the effect of the ma-length on the graph / analysis…

        • nexusstone says:

          Also, I have created columns in the PP data model having differing moving-average lengths (c.f., lagged: 1,3, 6 mos; & fwd/backward: 1, 3, 6 mos) for a 1.8M row revenues-ledger table. The 2013 .xlsb file has ballooned from ~220MB to over 400MB. Being able to ‘dynamically’ change the ma-period should COMPRESS ALL of this to just a FEW columns — (as well as make it MUCH easier to investigate / select the effects of the ma-length)…

      • BIlly says:

        to may it control ‘X months moving’ , how do i use slicer to replace the number of intervals(-3)? Please advise. Thanks a lot

        • powerpivotpro says:

          Just use the Search box at the top of the page Billy. “Moving Average Slicer” should do the trick :)

  2. Cezar says:

    I had a different problem: what to do if don’t know exact length of the period? Imagine the situation of some technology where you need to keep things in certain conditions over some period of time and it varies. Simply you have two dates START and END. In Excel it is not difficult using SUMPRODUCT or some kind of array stuff but it’s slow and COPY/PASTE routine is required each time you update. How to do it in DAX then when you keep process parameters in separate table? First thing, setting a relationship led me nowhere (in my opinion it is variation of MANY to MANY when you have parallel processes). I tried to apply USERELATIONSHIP but failed. The solution I found is simple but you need cumulative data. The formula of SUM over the period from A to B looks like: value(B)-value(A), AVERAGE: (value(B)-value(A))/(B-A). The funniest part is you don’t need any relationship at all, just take LOOKUPVALUE to find value(B) and value (A). Maybe this solution is obvious to everyone but for me was not but I found it refreshing.
    Greetings from Poland.
    Cezar
    Forgive me my English – I do not use it very often

  3. Soumitra Mukherjee says:

    Hi I Used the Fomula Calculated Field 1:=Calculate([Sellout Product Units],Datesinperiod([Date],lastdate([Date]),-3,Month)).. the Date feild in e.g.- 5/1/2013 12:00:00 AM, i got an error stating DatesinPeriod Function are only accepting date column reference as a first argument

  4. John says:

    Hi!

    I would love some help to do the “correcting for calendars that extend beyond the range of dates where you have sales”, as you write in the variations above.

Leave a Comment or Question