Power BI: Create a dynamic rolling average for trend analysis

A rolling average helps you better understand trends in your data by smoothing out fluctuations. In this article you’ll learn how to create a dynamic rolling average in Power BI that automatically moves along with your selected date range. This is essential for data and BI teams that want to perform accurate and flexible analyses.

What you need

• A date table linked to your fact table

• A measure (for example revenue or count) to which you want to apply the rolling average

• Basic knowledge of DAX formulas in Power BI

Step 1: Create a date table and ensure a relationship

A good date table is crucial for time-based calculations like a rolling average. Make sure you have a date table that contains all dates within your analysis period and that it is correctly linked to your fact table via a date column.

For example, use the following DAX formula to create a date table in Power BI:

DateTable = CALENDAR(DATE(2020,1,1), DATE(2024,12,31))

Then create a relationship in the model between DateTable[Date] and your fact table[Date].

Step 2: Define the rolling average measure

Create a new measure that calculates the rolling average over a dynamic number of days, for example 7 days. You do this with the DAX function AVERAGEX combined with DATESINPERIOD.

Use this DAX formula as an example:

RollingAverage7D = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD(
            'DateTable'[Date],
            MAX('DateTable'[Date]),
            -7,
            DAY
        ),
        CALCULATE(SUM('FactTable'[Value]))
    )
)

This formula calculates the average of the sum of the value over the last 7 days, with the period dynamically moving along with the selected date.

Step 3: Use the rolling average in your visualisations

Add the new RollingAverage7D measure to your reports, for example in a line chart together with the original value. This makes trends and fluctuations more visible and helps with making data-driven decisions.

You can adjust the period by changing the number -7 in the DAX formula to a different number of days, depending on your analysis needs.

Tips & common mistakes

• Make sure you have a complete date table without missing dates to guarantee correct calculations.

• Use relationships between the date table and fact table to make time intelligence functions work.

• Adjust the period in the DAX formula to make the rolling average flexible for different analyses.

• Avoid using filtered columns in the rolling average formula that can limit the dynamics.

Example / mini use case

Suppose you have a sales dashboard and want to show the 7-day average revenue to analyse seasonal influences and peaks. With the rolling average measure you immediately see whether revenue is rising or falling without daily fluctuations distracting you. This helps you make better forecasts and plan actions.

Conclusion

With this dynamic rolling average in Power BI you improve your trend analyses and make your reports more insightful and user-friendly. For creating these calculations, work directly in Power BI Desktop or the online environment.

Our tip: Place the rolling average next to the daily value in the same line chart. This way users immediately see the difference between the raw fluctuations and the underlying trend. Use a dashed line for the rolling average and a solid line for the daily values, so it remains visually clear which line shows which measurement.