Power BI Moving Averages with DAX, Quick Measures and AI

Power BI gives users multiple ways to calculate moving averages. You can write DAX , utilize Quick Measures, have Copilot write the formulas for you, or use new Visual Calculations. We’ll explain the various method available in Power BI to calculate moving averages, also called rolling averages.

Let’s jump in!

When to Use Moving Averages

A moving average is a statistical technique typically used to smooth out short-term fluctuations and highlight longer-term trends. Some of the most common scenarios for using moving averages on dashboards are:

  • Smoothing Time-series data that’s highly volatile.
  • Controlling for Seasonality where part of the year could mislead results
  • Combining multiple Moving Averages on a graph to identify changes in velocity

Methods to Calculate a Moving Average

There are several different methods for calculating a moving average in Power BI. You can use several different DAX formulas, the Quick Measures function, AI assistance with the help of Power BI Copilot, and newer Visual Calculations.

Before we start, make sure that your data model is appropriately setup and that a date table is present with an active relationship. Also ensure that appropriate data types are assigned to fields. Dates must be dates, numbers must be a numeric type etc. to avoid calculation errors.

Moving Averages with DAX AVERAGEX

The easiest way to calculate a moving or rolling average in Power BI is to use a combination of the AVERAGEX formula and the DATESINPERIOD DAX formula. The DATESINPERIOD formula will dynamically calculate the number of days in between the beginning and end dates. The AVERAGEX formula will automatically use the number of dates to calculate an average.

You have to make a dynamic formula so Power BI knows when to begin and end the calculation. The following example formula uses a field called Sales on a Sales table, and a field called Date on a Date Table.

Screenshot of a 7 day moving average calculated with the fields and formulas used in a Power BI report

The following is the calculation setup as a 7 day moving average that you can copy and paste into your own Power BI Dashboard. You will have to update the table and field references to match your own data.

7 Day Moving Average = 
CALCULATE(
    AVERAGEX('Sales',Sales[Sales]), 
    DATESINPERIOD(
        'Date Table'[Date], 
        LASTDATE('Date Table'[Date]), 
        -7, 
        DAY
    )
) 

The CALCULATE function in Power BI DAX tells Power BI that you are modifying filter context. It’s necessary for the formula to calculate a custom date range and to work correctly when using filters or slicers on the page.

Let’s look at another faster and quick method to create moving averages!

Moving Averages with Power BI Quick Measures

Quick measures in Power BI are pre-made measure templates. They let you drag and drop fields into a template, and Power BI generates the DAX formula on your behalf. Navigate to Table Tools > Quick Measure.

Screenshot of Power BI Quick Measure button

A panel will appear on the right side of the screen with a dropdown menu that lets you select the type of calculation you want to perform. Simple Moving Averages and Rolling Averages are the same thing. Select Rolling Average from the Dropdown list.

Selecting Rolling Average Quick Measre

Fill out the Quick Measure wizard on the right. The Base Value is the amount that you are summing or calculating the average of. The Date field needs to be the date field from the newly created date table. If the date column you are using is not on a specified date table, Power BI will produce an error asking you to update it.

Rolling Average Quick Measure template being filled out and generating DAX formula

The final section is to define the periods that you want the formula for, days, months, or years. Then there are additional options where you can calculate a trailing average that only looks back a certain number of periods or you could calculate a future looking rolling average with periods after.

When you click on Add, Power BI will generate the DAX formula in the top formula bar. Update the name of the new measure and it will be saved for use in other parts of your report and dashboard.

Moving Averages with Power BI Copilot AI

Suggestions with Copilot is a relatively new addition to Power BI. It uses natural language processing and Artificial Intelligence to translate a description of the measure that you want into DAX code. It’s available under the Quick Measure section, but instead of the Calculations tab, click on Suggestions with Copilot.

In the example below, we asked Power BI to create a moving average of the sales column for the past 7 days. When we wrote it, we were somewhat lazy and typed it in like we would type a request into Google, but the results are a working formula for a rolling 7 day average.

Using AI and Microsoft Copilot to calculate a moving average without knowing any DAX code

If you don’t have Power BI Copilot available, or it is greyed out there may be some setup required. We put together a Guide to Enable Power BI Copilot that you should take a look at. Some options require Power BI Admin support to turn on specific options if they’re not already enabled, but it’s a really cool tool and extremely helpful for building DAX formulas.

Power BI is a powerful analysis tool and gives people lots of granularity when creating visualizations. Let’s take a look at what a different type of moving average calculation, the exponential moving average.

Calculating Moving Averages with Power BI Visual Calculations

Power BI Visual Calculations were released as a preview feature in the February 2024 release of Power BI Desktop. The Visual Calculation feature doesn’t change the DAX formulas being used, but rather they allow you to utilize DAX templates and preview the results of the measure you’re writing.

To use Visual Calculations, you have to be running a recent version of Power BI Desktop.

Add a visual to the dashboard canvas and click on the “New Visual” button on the Power BI Ribbon.

Creating a new Visual Calculation to use a DAX template to calculate a Moving Average

The selected chart will go full screen, where you will see the chart at the top, the fields assigned to it on the right, and a formula builder at the bottom of the page.

Assign fields to your visual, and then press the Fx button on the formula bar to insert a template. In the example below, we use the MOVINGAVERAGE DAX function. As you fill it in with DAX, the results will populate below in a Matrix and above in the visual you’ve selected.

The Moving Average template selected in a Power BI Visual Calculation

Fill out the template and press enter. A new measure will be automatically generated where you can view the results in a matrix at the bottom of the page to check it for accuracy and a preview of the filled visual will appear at the top of the screen.

A Moving Average Visual Calculation Formula filled out to calculate based on the prior two periods

Microsoft publishes a guide on Using Visual Calculations in Power BI that explains some of the functionality and the different templates available.

Conclusion

Moving averages are an invaluable tool for trend analysis and smoothing short term volatility in your dataset. Power BI offers a robust set of features, making the calculation and visualization of moving averages straightforward and effective. Power BI gives you a number of options to complete this common task ranging from DAX, Quick Measures, AI powered Copilot, or Visual Calculations.

Scroll to Top