Sometimes reporting data values day-by-day can create the impression of dramatic ups and downs, when really what we want to see is the general trend of changes over time. We need, in other words, to create a chart based on moving averages. Such a measure is commonly used in tracking the price of stocks, but there are many other cases where showing, for example, 7-day moving averages is preferable to just showing the value for a given day. DAX in Power Pivot can handle this calculation quite easily. Here’s how, using the EARLIER function.
The Data Scenario
Our sample data consists of mobile phone device activations by mobile operators in different countries. That means our data set contains multiple devices, operators, and countries. We want to be able to calculate in our Power Pivot the value for 7-day average of activations for each distinct combination of device, operator, and country.
Here is a small subset of the data. We want DAX to determine for any given day, what is the 7-Day Average for # Activations.
|5/1/2013||Mobile SmartPhone A||United States||AT&T||25|
|5/2/2013||Mobile SmartPhone A||United States||AT&T||50|
|5/3/2013||Mobile SmartPhone A||United States||AT&T||75|
|5/4/2013||Mobile SmartPhone A||United States||AT&T||100|
|5/5/2013||Mobile SmartPhone A||United States||AT&T||25|
|5/6/2013||Mobile SmartPhone A||United States||AT&T||50|
|5/7/2013||Mobile SmartPhone A||United States||AT&T||75|
|5/1/2013||Mobile Phone B||France||Orange||65|
|5/2/2013||Mobile Phone B||France||Orange||75|
|5/3/2013||Mobile Phone B||France||Orange||85|
|5/4/2013||Mobile Phone B||France||Orange||105|
|5/5/2013||Mobile Phone B||France||Orange||113|
|5/6/2013||Mobile Phone B||France||Orange||125|
|5/7/2013||Mobile Phone B||France||Orange||137|
The math is easy: sum the values for Mobile SmartPhone A in US offered by AT&T for the last 7 days, and divide by the number of days:
So, for 5/7/2013, the 7 day average would be: (Sum the Activations for current day – 6 days )/ 7 or
(75 + 50 + 25 + 100 + 75 + 50 + 25) / 7 = 57
Use the Earlier Function along with a Custom Key to Identify the Distinct Records
In our Power Pivot, we need to be able to look up all of the activation values for a given mobile device, operator, and country combination for the relevant 7 prior days and then take the average value of those activations. Once again, we can thank Alberto Ferrari for a great solution. In his blog post, I discovered how to use the EARLIER function in order to calculate the moving average. (I simplify his approach here).
1.) In our scenario, we’re going to need to create a custom key by using simple concatenation to identify the Mobile Device & Operator & Country combinations.
2.) Then, we need columns to store the Start Date and End Date for the 7 day Period. The End Date is simply referencing the Date for the record, and Prior 7 Days is =[Date] – 6 days.
3.) We now have the parameters for our look up on all the data for this device – country – operator combination for the last 7 days. We can use the EARLIER function in another calculated column for 7-Day Average:
AVERAGE (MobileDevice[# Activations]),
MobileDevice[Date] >= EARLIER(MobileDevice[Prior7 Days])
&& MobileDevice[Date] <= EARLIER (MobileDevice[End Date])
&& MobileDevice[Key] = EARLIER (MobileDevice[Key])
The results of this DAX expression for the 7-Day Average for the record entered for 5/7/2013 correctly shows as 57:
How Does The EARLIER Function Work?
If you want to understand why the EARLIER function works here, it helps to see more examples in practice, such as the blog at Gavin Online or Javier Guillen, and of course the standard Microsoft help topic.
Thanks for reading! Questions? Comments? Contact us for more information or with other questions.