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

### 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:

**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] /

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:

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