How to calculate a Rolling Average in Power BI? DAX formulas

Tutorial with DAX formulas on Power BI rolling averages in to compare calculations over weekly, monthly, or quarterly intervals.

Tutorial to calculate a rolling average in Power BI, using 7 days, 30 days, quarters and years as time windows. From managers to business analysts, Power BI has become an indispensable tool for data-driven decision-making. But beyond mere data representation, there’s the need for deeper insights.

This is where rolling averages come into play. After mastering the calculation of previous month values in Power BI, which we covered in our last tutorial, it’s time to take a step forward. In this tutorial, we dive into how to create rolling averages using DAX formulas in Power BI. With a rolling average, you can smooth out fluctuations to identify trends, an especially useful tool for those in sales, finance, and operations management.

Why rolling averages matter in Financial Reporting?

Rolling averages provide a more accurate representation of data trends over a given time period, filtering out noise and volatility. This makes it an essential tool for time-series analysis in Power BI. Given the rich set of time intelligence functions available in Power BI, DAX (Data Analysis Expressions) can easily handle such calculations.

Set up a Power BI data model

For this tutorial, we’ll assume that you have a sales table with daily entries stored by the Sales Amount and DateKey columns, just like in our previous tutorial on calculating the previous month’s sales. Make sure you’ve set up a proper date table, a crucial step when dealing with time intelligence functions.

1. DAX formula to calculate a 7 day Rolling Average

The basic DAX formula to calculate a 7-day rolling average would look something like this:

SevenDayRollingAvg = 
AVERAGEX(
    DATESINPERIOD('DateTable'[Date], LASTDATE('DateTable'[Date]), -7, DAY),
    CALCULATE(SUM('SalesTable'[Sales]))
)

This formula averages the sales for each seven-day period ending with the current date. Note that 'DateTable'[Date] should be a continuous date column related to your sales data table.

2. Customize the Rolling Average period with days

The beauty of DAX is its flexibility. You can easily customize the formula for a 30-day rolling average by changing the -7, DAY in the DATESINPERIOD function to -30, DAY.

ThirtyDayRollingAvg = 
AVERAGEX(
    DATESINPERIOD('DateTable'[Date], LASTDATE('DateTable'[Date]), -30, DAY),
    CALCULATE(SUM('SalesTable'[Sales]))
)

3. Calculate a 3 months rolling average

When it comes to business analytics, sometimes it’s more useful to extend your outlook to quarters or even years. This is particularly beneficial for seasonal trend analysis or long-term strategic planning. Power BI’s DAX language makes it easy to adjust rolling average calculations to fit these longer time frames. For instance, to get a rolling average for a quarter, you could modify your DAX formula like so:

QuarterlyRollingAvg = 
AVERAGEX(
    DATESINPERIOD('DateTable'[Date], LASTDATE('DateTable'[Date]), -3, MONTH),
    CALCULATE(SUM('SalesTable'[Sales]))
)

By using -3, MONTH as parameters in the DATESINPERIOD DAX function, you can directly target the three previous months, which is a more standard representation of a business quarter. This ensures that you are capturing data in a way that aligns with how most businesses structure their financial reporting.

4. Previous Month’s 7-Day Rolling Average

Certainly, comparing rolling averages from different periods can offer valuable insights into trends or changes in performance. In Power BI, you can use DAX to calculate these comparative rolling averages easily.

Let’s assume you have a date table called DateTable and a sales table called SalesTable. You want to compare the current 7-day rolling average with the same metric from the previous month. To get the same 7-day metric but for the previous month, you can use an additional CALCULATE function to change the time context.

PrevMonth7DayAvg = 
CALCULATE(
    [SevenDayRollingAvg],
    PREVIOUSMONTH('DateTable'[Date])
)

5. Compare current and previous month 7-day average with DAX

Now that you have both metrics, you can make comparisons directly in Power BI visuals or continue your DAX calculations to quantify the difference or percent change. Here we use the SevenDayRollingAvg, calculated using a formula with the first example.

Difference = [SevenDayRollingAvg] - [PrevMonth7DayAvg]
PercentChange = DIVIDE([Difference], [PrevMonth7DayAvg], 0) * 100

With these DAX calculations, you can now create visuals that show the current 7-day rolling average, the previous month’s 7-day rolling average, the difference between the two, and the percent change. This advanced use case provides a robust way to compare time periods and make data-driven decisions.

6. Annual Reviews uses 12 Month Rolling Average

When assessing long-term performance or year-over-year comparisons, the 12-month rolling average is a go-to metric. It encompasses a full year of data, offering insights into annual patterns, seasonality, or longer-term growth trends.

To calculate this in DAX, the formula is:

AnnualRollingAvg = 
AVERAGEX(
    DATESINPERIOD('DateTable'[Date], LASTDATE('DateTable'[Date]), -12, MONTH),
    CALCULATE(SUM('SalesTable'[Sales]))
)

7. Troubleshooting

Occasionally, you might encounter errors while we calculate rolling averages in Power BI, often due to incorrect table relations or syntax errors. Just like in our previous tutorial about calculating the previous month’s value, always double-check the formula bar and highlighted errors to resolve issues.

For example, here are three of the most common errors:

“A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.”

This error typically arises when trying to use certain DAX functions directly within a filter context, which is not permissible. To resolve this, review your formula, especially any table filtering aspects, to ensure that unsupported functions are not used in filtering contexts. Instead, try using helper columns or modifying the structure of your formula to adhere to DAX best practices.

“The column ‘XXXX’ of the table wasn’t found.”

This one is a straightforward error that suggests a referenced column doesn’t exist in the table you’re referring to. This can happen due to typos, renaming columns, or restructuring your data model. To fix it, ensure the column name and table reference are correct. If you’ve made recent changes to the data model, consider refreshing the data source or adjusting the formula to match the current state of your tables and columns.

“Column ‘XXXX’ in table ‘YYYY’ cannot be found or may not be used in this expression.”

The last error of this short list usually pops up when there’s an issue with table relationships or if a column is not available in the current evaluation context. Firstly, check the relationships between your tables to ensure they’re set up correctly. If the relationships are fine, consider leveraging functions like RELATED() or RELATEDTABLE() to fetch values across linked tables, ensuring that the required columns are accessible in your formula context.

Conclusion on Power BI Rolling Calculations

Rolling averages are a powerful tool for anyone who wants to make sense of time-series data in Power BI. With this tutorial, you should now be equipped to set up and calculate any Rolling Average in Power BI to provide deeper insights into your data, following the DAX syntax and guidelines.

For more tutorials on DAX functions, remember to check out our Power BI tutorials section. In the previous posts we have seen how to calculate the last month values in Power BI.