DAX Made Easy - Rolling Averages
After spending significant time writing the perfect DAX expression to calculate rolling averages in Power BI, I was pleasantly surprised by the April Power BI Desktop release that included a slew of new pre-built "Quick Measures" that take much of the pain away from writing complicated DAX measures.
In this blog post I’ll explore this feature and explain how to add this and other Quick Measures to a report.
First off, depending on what version of Power BI Desktop you have installed, you may need to enable Quick Measures as it could still be a preview feature. To do this in Power BI Desktop, go to File > Options and setting > Options > Preview Features and check the "Quick measures" box. If you don’t see Quick Measures as an option here, you’re all set.
As an example dataset I’m just using a simple Excel spreadsheet with a 2-column table, containing about 8 months’ worth of exercise data:
I then created a new Power BI Desktop file and added a data connection to the spreadsheet above, leaving me with a very simple data model:
Adding a Rolling Average Measure
In the Fields pane on the right hand side in Power BI Desktop, find the "ExerciseMinutes" column and click the ellipsis (…) next to the field name. Select "Quick Measures" from the popup menu and you should see this dialog box:
Select "Rolling average" under Time Intelligence in the Calculation dropdown to expose this dialog:
Now do the following:
- Drag the Date field from the Fields list on the right into the Date field on the left.
- Select Months from Period dropdown (because we want to see an average across months)
- Enter the number of periods before and after the current month that you want included in the rolling average calculation
- Click OK
Now you should see a new measure in the Fields list:
Add the new measure to your visualization by clicking the “Line and stacked column chart” visual in the visuals palette to place a new visual on your canvas.
Set the Shared Axis to use the Date field, the Column Values to use the ExerciseMinutes field, and the Line values to use the new rolling average measure (“ExerciseMinutes rolling average” above)
You should wind up with something like this:
Now click the “Expand all down one level in the hierarchy” button (orange box above) – twice – to see the data at the month grain:
DAX Under The Hood
Behind the scenes, when you add a Quick Measure to your Power BI data model, Power BI is generating DAX to support the measure. The great thing is that you can actually see the underlying DAX by finding the measure in the field list and clicking on it to expose the formula bar.
Within the formula bar, you can easily edit the number of months you want to look backwards and forwards for the rolling average calculation.
One downside to this method I can identify is that it does not support rolling averages by week, since its not part of the Power BI-provided date hierarchy. However, you could leverage the DAX expression generated by the Quick Measure and your own date table that contains a week field to accomplish a rolling average by week.
The Quick Measures available today are just the start and we’ve heard the Power BI team plans to release many more in future releases so stay tuned!
Andy manages the BI Analyst, Project Management, and Quality Assurance teams at Inviso. Prior to joining Inviso he worked for 12 years at Microsoft as an IT Application Developer and Program Manager.