Power Pivot Principles: Dynamic Ranges and Periods for Rolling Averages
20 August 2019
Welcome back to our Power Pivot blog. Today, we expand on the rolling average concept, first introduced in a previous blog, and show you how to include a dynamic period selection range in a rolling average measure.
Heard of flogging a dead horse? We clearly haven’t…
Last week, we talked about creating a dynamic average range for a measure. As a recap, we used this measure to include a dynamic selection for the average range:
=CALCULATE(
AVERAGEX(SaleDataJul1, SaleDataJul1[Sum of Total Sales]),
DATESINPERIOD(
SaleDataJul1[Date],
MIN(
SaleDataJul1[Date]),
-MAX(
Days_Average[Days to Average]),
DAY
)
)
What if we also had to build in the ability to select the date range for our rolling average?
The first thing one would think of is to create a timeline slicer for our PivotTable
to select different dates on the slicer filters our PivotTable. However, our measure ignores the date filter and returns with the same moving average values:
We want the measure to calculate the rolling average starting from the first date in our date selection. We use the FILTER and ALLSELECTED functions to create a new measure that will incorporate date selection ranges into the rolling average calculation as follows:
=CALCULATE(
AVERAGE(SaleDataJul1[Total Sales]),
FILTER(
ALLSELECTED(SaleDataJul1),
SaleDataJul1[Date] > MAX(SaleDataJul1[Date]) - MAX(Days_Average[Days to Average]) && SaleDataJul1[Date] <= MAX(SaleDataJul1[Date])
)
)
In this measure we use the filter in the CALCULATE function, so we do not need to use the AVERAGEX function. The filter expression in this measure is a combination of two criteria combined with a double ampersand delimiter “&&” (the “and” operator). The first criterion
SaleDataJul1[Date] > MAX(SaleDataJul1[Date]) - MAX(Days_Average[Days to Average])
considers the later range of dates to average, less the number of days to the average from our input. The second criterion
SaleDataJul1[Date] <= MAX(SaleDataJul1[Date])
assesses the earlier range of dates to include in the average. With these two criteria combined the measure is able to take in the inputs of our date slicer and our Days to Average slicer to calculate our rolling average:
There we have it: a dynamic rolling average measure that can take inputs from both a date range to average and the number of days to average.
That’s it for this week, come back next week for more Power Pivot. Until then, happy pivoting!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our past Power Pivot blogs here.
