Moving Averages Controlled by Slicer

Two Month Moving Average is Pretty Smooth.  But Six Months is Smoooooother.
(Imagine Barry White Saying That:  “Ohhh Yeaahhh…  A Six-Month Moooving Average.  Smoooooth…”)

It’s already been a couple weeks back (yikes!), but I recently wrote a post on simple moving averages in Power Pivot.

One of the questions, in the comments, was how to control the “length” of the moving average dynamically:

What’s That???  Oh No!  That’s DISCONNECTED SLICER’s Music!!
(Yes That’s a Reference to [link removed due to 404] Pro Wrestling Entrance Music, A Fascinating Read)

Hey, when TWO people ask for something, and one of them deploys CAPITAL LETTERS in the effort, and then pairs said uppercase with one of my favorite words “(“enhance”), well, I’m hooked.

Today We Do Dessert First!

Let’s work backward from the result, shall we?

The Slicer Controls the Length of the MA Period AND The Chart Title.  Now That’s SMOOOOOTH!

Another Disconnected Slicer?

Yes, yes indeed.

First I created a table in normal Excel, and copied it to the clipboard:

Pasted it into Power Pivot:

Resulting in this table:

I wanted the slicer tiles from the “Months to Include” column to sort sensibly, so I added a calc column:

Then I can put the slicer on my pivot, and it sorts in my desired order:

The “Harvester” Measure

[Selected MA Length] =

  MAX(‘MA Length'[Number of Months])

Yielding a situation like:

Variable Moving Sum and Average Measures

Now it’s time to do something with that harvester measure.

[Variable Moving Sum] =

   CALCULATE([Units Sold],
             DATESINPERIOD(Calendar[Date],
                           LASTDATE(Calendar[Date]),
                           [Selected MA Length],
                           Month
                          )
            )

The highlighted section is the only difference between this measure and the original moving sum from my previous article.  Previously, that part was “hardwired” to –3, to give us a 3-month moving average.

[Variable Moving Average] =

   [Variable Moving Sum] /

   CALCULATE(DISTINCTCOUNT(Calendar[Year Month]),
             DATESINPERIOD(Calendar[Date],
                           LASTDATE(Calendar[Date]),
                           [Selected MA Length],
                           Month
                          )
            )

Again, the highlighted portions are the only differences between this measure and the fixed 3-month MA.

A Bug…

If the user of this report/dashboard picks one of the “forward” options on the slicer, the current month is NOT going to be counted, whereas it IS counted in the “back” options.

Why is it not counted for “forward?”

Here’s the moving sum formula again, and I will highlight the offending section:

[Variable Moving Sum] =

   CALCULATE([Units Sold],
             DATESINPERIOD(Calendar[Date],
                           LASTDATE(Calendar[Date]),
                           [Selected MA Length],
                           Month
                          )
            )

When we go backward from LASTDATE of the current month, the current month is included.  But when we go forward, well, the current month is not included.

So we need an IF that checks to see if [Selected MA Length] is positive, and if so, switches the LASTDATE to a FIRSTDATE.

This post is already running long, so I will leave that as a, um, homework assignment

The Chart Readout

The last thing to do is make the chart title readout:

Select the Chart Title, Type an = in the Formula Bar and Pick a Cell (G6 in this case)
The formulas above G6 are used to construct G6 itself
(Click for Larger Version)

Voila