How to Calculate Rolling Average in Power BI

Trying to make sense of your daily sales, traffic, or lead data can feel like looking at a wild scribble. Some days are up, some are down, and finding the actual trend in all that noise is a challenge. That's where a rolling average comes in - it’s a simple but powerful technique to smooth out those volatile fluctuations. This article will walk you through exactly how to calculate and visualize a rolling average in Power BI, step-by-step, using a bit of DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Rolling Average, Anyway?

A rolling average, also known as a moving average, calculates the average of data points over a specific, sliding time period. Instead of averaging all your data into one big number, it averages the last 7 days, then the 7 days before that, and so on. Think of it like looking at your data through a 7-day window that moves forward one day at a time.

Let's say you're looking at daily sales for your Shopify store:

• On January 7th, the 7-day rolling average calculates the average sales from January 1st to January 7th.
• On January 8th, the window slides forward. The calculation now averages sales from January 2nd to January 8th.
• This process repeats for every day, creating a new, smoother data point that represents the recent trend.

This is incredibly useful for a few reasons:

• It Smooths Out Noise: It minimizes the impact of unusually high or low days (like a massive Black Friday sale or a random Tuesday with zero transactions), giving you a clearer picture of performance.
• It Highlights Trends: With the daily distractions gone, you can more easily see if your sales are trending up, down, or staying flat over time.
• It Provides Contextual Insights: Marketing reports often use moving averages to see patterns. For a SaaS business, for instance, a 7-day rolling average of user sign-ups shows momentum more clearly than volatile daily numbers. The same goes for an online publisher and a 28-day moving average of user engagement.

Step 1: Get Your Data Model Ready

Before writing a single line of DAX (the formula language for Power BI), you need to make sure your data model is set up correctly. This is the most common place where people get stuck. There are two non-negotiable requirements for time intelligence functions to work in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

You Need a Dedicated Date Table

A date table is just a simple table that contains a continuous list of dates, with one row for every day. Even if your sales data has gaps (for instance, no sales on a Sunday), your date table must include that Sunday. This continuous timeline gives Power BI a reliable framework for calculations like rolling averages.

You can create a date table in Power BI with a simple DAX command. Go to the "Modeling" tab in Power BI Desktop, click "New Table", and enter one of the following formulas:

Method A: CALENDARAUTO()

This function automatically scans all the date columns in your model and creates a table that spans from the earliest date it can find to the latest date.

_Date = CALENDARAUTO()

Method B: CALENDAR()

This method gives you more control, letting you define the start and end dates manually. This is useful if you want to project into the future or limit the date range.

_Date = CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31))

After creating your date table, right-click on it in the Fields pane and select "Mark as date table." A dialog box will pop up, choose the primary date column from your new table and click OK. This step is critical and tells Power BI to use this table for time-based calculations.

You Need to Create a Relationship

Once your date table exists, you must connect it to your main data table (often called a 'fact table'), like your Sales, Traffic, or Orders table.

Go to the "Model" view in Power BI. You'll see boxes representing your tables. Find the date column in your date table (e.g., _Date[Date]) and drag it onto the corresponding date column in your main data table (e.g., Sales[OrderDate]). A line will appear between them, signifying the relationship is active. Your model is now ready.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Write the DAX Formula for Your Rolling Average

Now for the fun part. We’ll create a "measure" to calculate our rolling average. A measure is a dynamic calculation that changes based on the filters or context in your report (like the date on a chart).

Let's assume you already have a basic measure for your core metric, like Total Sales:

Total Sales = SUM(Sales[Revenue])

Right-click on your Sales table in the Fields pane and select "New measure." In the formula bar, paste the following DAX formula. This will calculate a 7-day rolling average of total sales.

7-Day Rolling Avg Sales = 
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        '_Date'[Date],
        LASTDATE ( '_Date'[Date] ),
        -7,
        DAY
    )
)

Walking Through the Key Functions:

• CALCULATE(): This is Power BI's super-function. Its job is to evaluate an expression (like [Total Sales]) but with a modified filter context. In our case, instead of looking at just a single day, we're telling it to look at a 7-day period.
• DATESINPERIOD(): This is the function that actually creates our "moving window." It returns a table of dates. Let’s look at its arguments from the formula above:

After pasting the code, press Enter. Your new measure, "7-Day Rolling Avg Sales," will appear in the Fields pane.

Step 3: Add Your Rolling Average to a Chart

The best way to see the smoothing effect of a rolling average is to put it on a line chart next to the original, choppy data.

1. Select a Line chart visual from the Visualizations pane.
2. Drag the main Date column from your _Date table onto the X-axis field.
3. Drag your original measure (e.g., Total Sales) onto the Y-axis field.
4. Now, drag your new 7-Day Rolling Avg Sales measure onto the Y-axis as well.

You should now see two lines. One will be spiky and erratic - that's your daily sales. The other line will be much smoother, moving elegantly across the chart - that's your rolling average. This second line makes it far easier to spot the true upward or downward trajectory of your performance, free from daily volatility.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Customizing Your Rolling Average and Solving Common Problems

Once you have the basic formula down, it's easy to adapt it. And most problems that crop up are solved easily if you follow a few basic rules.

Changing the Time Period

Want a 30-day or 90-day rolling average? It's simple. Just change the -7 in your DAX formula to a number of your choosing, say to -30:

30-Day Rolling Avg Sales = 
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        '_Date'[Date],
        LASTDATE ( '_Date'[Date] ),
        -30,
        DAY
    )
)

Common Power BI Problems I See All The Time: Answered

• "My rolling average line is flat." This almost always means the relationship between your date table and your main data table is missing or inactive. Go to the Model view and confirm there's a solid line connecting them. Also, double-check that you've marked your date table as "the official date table."
• "I'm getting an error about a single value for an entire column." You may have accidentally supplied only a given column during your calculation for LASTDATE. This is usually a result of dragging a complete table instead of a specific column. So check the inputs and use filters to ensure you're referencing distinct columns rather than tables where possible.
• What if I don’t have data on a particular day? Power BI can handle unexpected scenarios thanks to the DAX language. You can do more error handling when needed, but in our case, we can use an IF statement. Essentially say, "IF itʼs a blank, display nothing."

Final Thoughts

Calculating a rolling average in Power BI is a great way to cut through daily noise and see the trends that actually matter. By setting up a dedicated date table and using a classic DAX pattern combining CALCULATE and DATESINPERIOD, you can add a powerful layer of analysis to your dashboards with minimal fuss.

While learning DAX is rewarding, it can often feel like you need a data analyst's skills just to answer a simple question. We built Graphed because we believe understanding your data shouldn't be so complex. Instead of building data models and learning formulas, you can connect your sources like Google Analytics or your CRM and simply ask, "show me my 7-day rolling average of website sessions for the last 30 days." We generate the right visualization on a live dashboard for you in seconds, so you can focus on making decisions, not building reports.