How To Calculate Rolling Average In Power Bi

by

In Power BI, you can calculate rolling averages using several DAX functions. Let’s consider an example to explain in detail.

Suppose you have a dataset with a column named “Sales” which contains the sales figures for each day. Now, you want to calculate the 7-day rolling average of these sales values.

To calculate the rolling average, follow these steps:

  1. Create a new measure by clicking on “New Measure” in the Modeling tab.
  2. In the formula bar, enter the following DAX formula:
    
      Rolling Average = 
      AVERAGEX(
        DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -7, DAY),
        'Table'[Sales]
      )

Here’s a detailed explanation of the formula:

  • DATESINPERIOD function is used to create a date range based on the maximum date in the ‘Date’ column and the last 7 days.
  • AVERAGEX function calculates the average of the ‘Sales’ column within the created date range.

After creating the measure, you can add a visual element (such as a line chart or a table) to visualize the rolling average.

That’s it! You’ve now learned how to calculate rolling averages in Power BI using DAX functions.

Read more

Leave a comment