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.