# Calculating A Rolling Average In Power BI Using DAX

In this tutorial, Iâ€™m going to show you how to calculate a dynamic Rolling Average in Power BI using the ALLSELECTED function. You may watch the full video of this tutorial at the bottom of this blog.

Rolling Averages can give you valuable insights into a variety of data, especially in Sales.

This dashboard shows how many sales are made per day and the amount of revenue earned

Thereâ€™s a lot more volatility within the short-term period, but it eventually evens out in the long-term. It becomes a static line because the Average has more dates to work with over time.

This unique insight that you can get using Rolling Averages can hold a lot of value to certain scenarios.

## Understanding The Formula Logic

Letâ€™s go to the example.

First, Iâ€™m going to set up my dates and turn it into a slicer. Then, Iâ€™ll create a table containing the Dates and Total Sales.

The table now shows Sales per day.

Getting your measures into table makes it easier to see whatâ€™s going on in the calculations.

Iâ€™m going to calculate a Rolling Average through time which updates automatically based on the selected time frame.

So, letâ€™s go to the calculation.

This is the formula for the Rolling Average Sale:

Now, what you want to achieve is to be able to dynamically look back on every single Sales per day prior to a certain day and get their average.

And as you move down to the other rows, you need to create this time window endlessly. So, the time window essentially expands for every day as you go down the list in the table.

In the table you can see that the Rolling Average on the first day is the same as the Total Sales.

This is because thereâ€™s no previous value. The Total Sales will always have the same value as the Rolling Average during the first day.

But on the second day, it becomes the average of the first two days. On the third day, itâ€™s the average of the first three days, and so on. This continues until the last row in the table.

## DAX Functions In The Formula

This is the key part of the formula:

This opens up the specific time window. It works through every single date in the current selected context. This is what the ALLSELECTED function does.

Then, it works out if the Date is less than or equal to the MAX Date. The MAX Date is always the current date as you go down the list in the table. It works out which days evaluate to TRUE.

If they do evaluate to TRUE then it will generate the table that itâ€™s going to iterate through.

For all those Dates that evaluate to TRUE, the formula calculates the Average of those Dates. So, you get every single result using the same calculation. The time window just becomes larger because youâ€™re looking across a larger range of Dates.

In the chart, you can see that the dark blue line shows a big change from the start which then eventually evens out over time.

And if you change the date range, it will continue to show the similar trend.

The Average starts high but then evens out over time.

## ALLSELECTED Vs ALL Function For Rolling Averages

When calculating a Rolling Average, use ALLSELECTED instead of using ALL.

Using the ALL function in the formula would give you weird results.

Hereâ€™s an example.

You can see the chart shows odd numbers. It also has a different y-axis. Making both of the axis start at 0 and adjusting the End value of the chart will give you a result with a flat line.

This happens because itâ€™s working across every single day through time. The date selection is not being taken into account. Itâ€™s going back to the beginning of time.

Thatâ€™s why ALLSELECTED is key.

## Conclusion

The dynamic Rolling Average calculation in Power BI isnâ€™t that difficult. It simply requires an understanding on what you can do with iterating functions and virtual tables.

This technique has many unique uses which you can apply across a wide range of scenarios.

Hopefully, you learned a lot about the logic behind how the dynamic Rolling Average works.

Talk to you soon,

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...