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:
And a simple pivot:
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.
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] =
And see what that looks like:
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:
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] /
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.”
This Calc is More “Fair” to the Months at the Beginning
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.