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

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 25 Comments

1. Jeff Lingen says:

Brilliant!

2. Chris Gilbert says:

Is it possible to “go one better” and be able to have the [Variable Moving Sum] measure of interest be a slicer selection, as well?
For instance, if I have measures for [Units Sold], [Cases Sold], and [Pallets Sold], can I choose which of those three measures to graph and calculate a moving average for?

1. powerpivotpro says:

ABSOLUTELY you can! Because disconnected slicers are PURE MAGIC.

Seriously, if you are reading this and have yet to do some crazy, mad scientist-level shit with disconnected slicers… RUN, don’t walk, to your nearest workbook and start experimenting. It will change your view of the tool and the world of data.

So imagine a two-column disconnected table of captions and ID’s. “Units,” 1, “Cases”, 2, “Pallets”, 3

Then a harvester measure that is the MAX (or MIN, whatever) of that ID column.

Then a third measure that is a SWITCH on that harvester:

SWITCH(harvester measure here,
1, [Units Sold],
2, [Cases Sold],
3, [Pallets Sold]
)

Then substitute the switch measure in wherever [Units Sold] appears in the post above. Bam! 🙂

This is quite similar to the sort by slicers trick, BTW:

http://www.powerpivotpro.com/2011/10/user-friendly-report-sorting-with-slicers/

1. Chris Gilbert says:

That’s AWESOME!

1. powerpivotpro says:

Time for a Pulp Fiction quote:

“I KNOW BABY! You’d dig it the MOST!” 🙂

2. Wayne Ivory says:

Ha ha! It’s easy to tell when you’re getting excited. Apart from quoting Vincent Vega in 2010 this appears to be the first time you’ve sworn on this site. 🙂

1. powerpivotpro says:

A delightful observation! I wonder if it’s Vincent Vega himself taking the keyboard 🙂

In all truthiness, my day to day language is relatively coarse. At Microsoft, in the “old days,” profanity was used like punctuation – and not just as exclamation points. Nope – commas, periods, question marks… whatever. At the height of my powers I was even able to express Unicode punctuation like the tilde and the umlaut, using nothing but English profanity!

Now here’s the important question Wayne… did you just conduct an extensive google search of my site looking for common profanities? Because if so, that is AWESOME 🙂

3. This is COMPLETELY MINDBLOWING — (with the possibilities) !!!

The entire discussion / thread is INCREDIBLY VALUABLE !!!

Thanks MUCH for you INSPIRING guidance & stimulation…

4. How can you ‘feed’ a (variable) slicer list — to the SWITCH() — (or other such ‘switching’ / selecting function) ???

[ Imo, ANYthing that is a ‘hard-coded’, fixed list (of options / selections, in this case) is INflexible — & soon ‘outdated’ — (such as is shown in the SWITCH() list above)… ]

Thanks for the next iteration — & majik…

1. powerpivotpro says:

For multiple slicers, see my other recent comment.

For making the choices on the slicer ITSELF variable, I highly recommend using a database. You can’t change the values displayed on a slicer without re-importing or refreshing the underlying table. For this purpose, dynamic database logic is a big winner.

1. I don’t know ANYTHING about DDL. What are some GOOD references re this — & how would it be implemented in a PowerPivot context / situation ???

I will say that ANY time that the Data Model (DM) needs to be refreshed, it’s a MAJOR PAIN — & takes a LONG time (~30-min in our case). Also, the Excel / PowerPivot workbooks seem to CORRUPT themselves fairly often — so, I have to keep MANY backups — & keep rolling back to prior ‘good’ versions — in order to ‘stay afloat / alive’…

5. On the ‘go one better’ track — I would SPLIT the selection list / options into TWO separate lists — a FORWARD list — & a BACK(ward) list…

This would give even MORE flexibility of exploring / using different combinations moving-average periods (both forward AND backward) to better fit & explore / view the data. [ WHY: Because some (line-item) data is pretty consistent / smooth — & some is (very) rough / variable. The same averaging period isn’t useful for ‘everything’. ONE view of the data does NOT ‘fit all’. So, being able to EASILY find the ‘best’ combo is VERY VALUABLE / USEFUL. ]

Also, as mentioned in a(nother) of my comments, selecting different combos of selections off of multiple slicers provides EXTREME FLEXIBILITY to the analysis & display of data. For example, we are in the real estate business — so, displaying analytic graphs — created by selecting (from our PowerPivot OperStmts) for various combos of: ENTITIES; PERIOD(s); GLA-ACCTNUMs/GROUPS; measures (based upon: abs\$s; per-sq-ft; per-unit); etc. provides a VERY FLEXIBLE analytic / display forum…

1. powerpivotpro says:

The trickiest part about using separate slicers is that you have to “ignore” all but one of them, correct?

So, in English, your formula could be something like:

If there’s more than one slicer tile selected on Slicer A
True: If there’s more than one slicer tile selected on Slicer B
True: Give up and tell the user they need to select one value somewhere
False: Use slicer B’s harvester measure to drive your calcs
False: Use slicer A’s harvester measure to drive your calcs

Although the case where the user selects a single value on BOTH d/c slicers is also ambiguous I guess.

1. My ‘concept’ would be to use BOTH slicers — one for the lead (Forward) — & the second for the lag (Back). The DatesBetween (or DatesInPeriod) would be the desired lead-lag interval. If you had no lead / lag — you would select (or default to) the ‘0’ position / option. You also would need something to REQUIRE SINGLE selections in each slicer (or build some other ‘protections’ for multiple selections in a single slicer)…

2. Chris Gilbert says:

Regarding…
You also would need something to REQUIRE SINGLE selections in each slicer (or build some other ‘protections’ for multiple selections in a single slicer)…

That’s what creating your “Slicer Harvesting Measure” by using the MIN() or MAX() function is all about. You may want to use a Slicer Table naming convention that incorporates “MIN_” or “MAX_” into the “title”, so that the end-user has some clue about the underlying operation that goes on when they select a slicer entry.

Also, you might be able to take advantage of the SWITCH() function’s final ELSE_argument to force a choice should no selections be made (the slicer “cleared” with the X’d-out funnel icon).

3. powerpivotpro says:

Ah, OK! So it’s *kinda* like the first part of this post:

http://www.powerpivotpro.com/2011/04/ab-campaign-analysis-with-start-end-date-slicers/

Except that it wouldn’t use fixed dates, and therefore always be relative. VERY cool.

Question: when would that technique make sense in your work? Are there ever cases when you want to do, say, a 5 month MA that is the current month, the previous 3 months, and the 1 following month?

4. [ I see that this thread allows nesting to only a limited level… ]
So, I am responding a level above (to your question a level below)…

In any event, as stated above:

Being able to ‘EXPLORE’ & select various levels of moving-average periods (both lead / forward & lag / backward) would give even MORE flexibility of exploring / using different combinations moving-average periods to better fit & explore / view the data (for, in our case, various GLA-ACCTNUMs, ENTITIES, PERIODS (YYYYMM), measures (abs\$; perSqFt; perUnit), etc. — between & across various entities & GLA-ACCTNUM data-series (& various ‘higher-level’ groupings of these)….

>>> NOTE the following:
WHY: Because some (line-item) data is pretty consistent / smooth — & some is (very) rough / variable. The same averaging period isn’t useful for ‘everything’. >>> ONE view of the data does NOT ‘fit all’.
>>> Ergo, being able to EASILY find the ‘best’ combo is VERY VALUABLE / USEFUL…
>>> When you get 5-6+ lines on a graph, especially, if the lines are NOT SMOOTH(ed) (i.e., using — moving averages — &/or trendlines of the moving averages or other series) then it becomes CONFUSING, UNREADABLE & MEANINGLESS…

I haven’t opened up the discussion (yet) about trendlines & vba — but, imo, other tools in the arsenal should include the ability to use trendlines. [ Of the various combinations: generally, I have found polynomials of the 3rd & 4th power to be the most useful; for OUR operations (& OUR data), log, straight-lines functions are NOT useful. And, the moving averages (while useful) are ‘duplicative’ of the present thread / slicers, et al. ]

(a) Can you create trendlines (of various powers, et al) using slicers ???

(b) To what extent (& how) can you use VBA (or other) to ‘ENHANCE’ — & EXTEND — ALL of this stuff ???

[ But, I think, now, I am taking TOO many steps at one time — so, we probably should go back to the (immediate) discussion / topics at hand — which, I believe, was (simply) slicers & moving averages… ]

3. Lars Warvik says:

Very nice!
To handle the months at end of 2013 without data I have modified the ‘Variable Moving Sum’ by checking for Units sold in every month. This gives you a even nicer graph which is cut at your last Year-Month.

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

1. powerpivotpro says:

Yes! An excellent improvement indeed 🙂

4. powerpivotpro says:

Thanks, everyone, for the comments. I think I’ve learned something significant here actually. Maybe we should do a Disconnected Slicers Week or something similar.

1. Philip Carter says:

Hi, very nice!
I’m trying to implement this for an HOURLY comparison, but typing in “Hour” instead of “Day” returns an error. Have you tried this sort of time comparison?

Cheers,

Phillip

1. powerpivotpro says:

The time intel functions only work down to the date level. There is no handling for “sub day” timeframes like hours or minutes.

But Hours can be made to work, using a custom “calendar” table and the FILTER() and ALL() functions. Search this blog for “GFITW,” or “Custom Calendars” and you will find some starting points.

5. Steven Oden says:

Is there any way I could get a copy of this workbook just to look at the model. It doesn’t seem to be clicking for me just reading it on the page.

6. Harry Cuntapay says:

thanks a lot for this. Initially I struggled to implement this. Only after changing my primary keys to be in the date/time format and not on any artificial keys in the calendar(continuous) and all the related tables I was able to make it work.

7. Andre says:

Hi Rob, Excellent post. Have you have done a similar post for custom calendars? I’ve been playing around with the GFITW (it is a truly great formula) but can’t get it to do what I need. My requirements are essentially exactly the same as this post, but for a custom calendar (201601, 201602, 201603… etc. representing Yearmonth values).