# 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

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:

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.

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

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

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

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

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.

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.

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

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.

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

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.

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],
__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]))
)
)``````

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

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.

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

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: