How to Calculate Rolling Average Using Power BI DAX?

This Power BI tutorial helps you to understand how to calculate rolling average using Power BI DAX.

Recently, I worked on a Power BI Report where I needed to find the average of a series of values over time. With the help of rolling average measures, I easily find my requirements.

In this tutorial, I will explain what a Power BI rolling average measure is and how to calculate a rolling average using Power BI DAX.

What is Rolling Average Measure in Power BI?

A rolling average measure in Power BI is a calculation that gives you the average value of a particular metric over a specific period; it is also called the moving average moving average measure in Power BI.

For example, if you’re tracking monthly sales, a rolling average of 2 months would continuously update to show the average sales over the most recent 2-month period.

How to Calculate Rolling Average Using Power BI DAX

How to Calculate Rolling Average Using Power BI DAX

Now, I will tell you how to calculate the rolling average of a measure in Power BI.

Here, I have a SharePoint list (Orders) that contains below columns with various data types:

ColumnsData Types
Order DateDate and time
Product NameSingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
SalesCurrency
ProfitCurrency
Rolling total measure power bi

Follow the below steps to create a rolling average measure in Power BI. Here we see the rolling average for 12 months:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

moving average measure in power bi

2. Under the Home tab, click the New measure.

rolling average count power bi

3. In the formula bar, put the below expression.

Total Sales = SUM(Orders[Sales])

Where:

  • Total Sale = Name of the Measure
  • SUM = DAX Function
  • Orders = Name of the Table
  • Sales = Name of the Column
how to calculate rolling average in power bi

4. Under the Home tab, click the New measure.

rolling average count power bi

5. In the formula bar, put the below expression.

Rolling Average Of 12 Month = 
AVERAGEX(
		VALUES('Orders'[Order Date].[Month]),
		[Total Sales]
	)

Where:

  • Rolling Average Of 12 Month = Name of the Measure
  • AVERAGEX = DAX Function
  • VALUES = DAX Function
  • Orders = Name of the Table
  • Order Date = Name of the Column
  • Total Sales = Name of the Measure
Rolling Average Of 12 Month in power bi

The “Rolling Average Of 12 Month” measure computes the average total sales over 12 months, dynamically adjusting with new data by iterating monthly sales and average.

6. Click the Home tab -> Expand Visual gallery(black box) -> Click the Matrix visual.

how to average measure in power bi

7. Then, using the +Add data option, add the Order Date into the Rows field and Sales and Rolling Average Of 12 Month into the Values field.

Power BI DAX Tutorial Moving Rolling Average measure

8. Now, you see the 12-month average in the Power BI Matrix visual.

Rolling Average of a measure in Power BI

This way, you can calculate the rolling total of a measure in Power BI.

Power BI Rolling Average Quick Measure

Now, I will tell you how to create a rolling average using the quick measure in Power BI.

1. Under the Home tab, click the Quick measure.

quick measure rolling average power bi

2. Then a Quick measure pane opens, expand the Select a calculation. Select the Rolling average.

power bi 2 month rolling average quick measure

3. Using the +Add data function to add the ‘Base value‘ as ‘Sales,’ ‘Date‘ as ‘Order Date,’ and ‘Period‘ as ‘Months.’ Set the ‘Periods before‘ to ‘2‘ and the ‘Periods after‘ to ‘0‘. Then, Add a button.

How To Calculate A Rolling Average In Power BI Using measure

4. Then you see the Total Sales rolling average (Quick measure) added in the Data pane.

When selected (Total Sales rolling average), you can observe the DAX expression in the formula bar automatically generated.

Total Sales rolling average = 
IF(
	ISFILTERED('Orders'[Order Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('Orders'[Order Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'Orders'[Order Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('Orders'),
					'Orders'[Order Date].[Year],
					'Orders'[Order Date].[QuarterNo],
					'Orders'[Order Date].[Quarter],
					'Orders'[Order Date].[MonthNo],
					'Orders'[Order Date].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE([Total Sales], ALL('Orders'[Order Date].[Day]))
		)
)
Rolling Averages In Power BI

5. Click the Home tab -> Expand Visual gallery(black box) -> Click the Matrix visual.

how to average measure in power bi

6. Then, using the +Add data option, add the Order Date into the Rows field and the Sales and Total Sales rolling average into the Values field.

How to create a dynamic rolling average measure

7. After completing the previous steps, observe that the matrix visual displays a 2-month rolling average in Power BI.

power bi rolling average quick measure

This way, you can use rolling average quick measure in Power BI.

Conclusion

I hope this tutorial helps you to understand the Power BI rolling average.

In this tutorial, we learned what a Power BI rolling average measure is and how to calculate the rolling average of a measure in Power BI. Additionally, we will see how to create a rolling average using quick-measure suggestions in Power BI.

Furthermore, you may like some more Power BI articles: