Call Us: 425.702.8175
Follow Us:

Blog

News, tips, and happenings at Inviso

DAX Made Easy - Rolling Averages

  • by Andy Parker
  • August 28, 2017
DAX Made Easy - Rolling Averages

Introduction

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.

Initial Setup

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.

EnablePreview

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:

ExcelTable

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:

PBITable_relationshipView

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:

QuickMeasuresMenu

Select "Rolling average" under Time Intelligence in the Calculation dropdown to expose this dialog:

RollingAveragesProperties

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:

NewMeasureInFieldList

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:

BarChartInitialState

Now click the “Expand all down one level in the hierarchy” button (orange box above) – twice – to see the data at the month grain:

BarChartMonthGrain

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.

FormulaBar

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 Parker

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.

DAX Quick Measures

Subscribe to our newsletter

© 2017 Inviso Corporation. All rights reserved.