Business Intelligence

Rolling Averages in Power BI

By August 28, 2017 April 11th, 2019 No Comments
bar chart image


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.

preview features image

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:

Excel table image

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:

Power BI table relationship view image

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:

quick measure menu image

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

rolling average properties image

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:

new measure in fields list image

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:

bar chart initial state image

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

bar chart month image

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.

DAX formula image

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

Author Andy Parker

More posts by Andy Parker