How to Calculate Moving Average in Power BI

Calculating a moving average in Power BI is one of the best ways to smooth out noisy data and see the real trend hiding beneath daily fluctuations. If your sales, web traffic, or performance metrics look like a wild rollercoaster, a moving average can reveal the underlying direction. This article will walk you through exactly how to create a moving average measure using DAX, from preparing your data to visualizing the final result.

What is a Moving Average and Why is It Useful?

A moving average, also known as a rolling average, calculates the average of a metric over a specific number of previous periods. For example, a 7-day moving average of sales will, for any given day, show the average sales for that day and the six days prior. As each new day passes, the oldest day is dropped from the calculation, so the average "moves" along with time.

This technique is incredibly popular in business analysis for several key reasons:

• It Smooths Out Volatility: Daily data can be chaotic. A sudden spike or dip can be distracting and misleading. A moving average smooths these brief fluctuations, making it easier to see the legitimate, sustained trend.
• It Highlights Long-Term Momentum: Are your sales generally trending up, even with some bad weeks? Is your marketing performance slowly declining, despite occasional good days? The moving average line makes this long-term momentum much clearer than raw daily numbers.
• It Helps Identify Patterns: By looking at smoothed data, you can more easily spot seasonality, cyclical patterns, or the real impact of a new marketing campaign without getting distracted by day-to-day noise.

Common examples include a 7-day moving average to track weekly performance, a 28-day moving average for monthly trends, or a 90-day moving average for quarterly analysis.

Prerequisite: Set Up a Proper Date Table

Before writing a single DAX formula for time-based calculations, you must have a dedicated date table. Time intelligence functions in DAX rely on a complete, contiguous set of dates to work correctly. Your main data table (e.g., your sales records) might have gaps on days with no sales, which will break the calculations.

A proper date table should:

• Have a column with a unique date for every single day in the period you're analyzing (no skipped days).
• Contain dates that span the full year(s) of your data, from January 1st to December 31st.
• Not have any null or blank values in the date column.

If you don't already have one, creating one is simple.

How to Create a Date Table with DAX

1. Navigate to the Data view in Power BI Desktop (the second icon on the left pane).
2. From the Home tab in the ribbon, click New Table.
3. Enter the following DAX formula into the formula bar. This example creates a table with dates from January 1, 2021, to December 31, 2024. Adjust the years to fit your data.
4. (Optional but Recommended) Add useful columns like Year, Month, and Quarter to your new Date table. With the table still selected, click New Column and add these formulas one by one:

After creating your table, you need to tell Power BI that this is your official date table:

1. Right-click on your new "Date" table in the Fields pane on the right.
2. Hover over Mark as date table and select Mark as date table again from the submenu. In the dialog box, select your primary date column.

Finally, go to the Model view (the third icon on the left) and create a relationship by dragging the date column from your main data table (e.g., Sales[OrderDate]) onto the date column in your new Date table.

Calculating a Simple Moving Average with DAX

With your date table ready, it’s time to create the moving average measure. We'll start with a classic 30-day moving average for sales.

Step 1: Create a Basic Sales Measure

Before averaging sales, you need a basic measure that sums them up. This is a crucial best practice in Power BI. Right-click on your main data table (e.g., "Sales") and select New Measure. Enter:

Total Sales = SUM(Sales[SalesAmount])

Using base measures like this makes your other DAX formulas cleaner and more manageable.

Step 2: Create the Moving Average Measure

Now for the main formula. Right-click your Sales table again and choose New Measure. Let’s build a flexible 30-day moving average.

30-Day Moving Average Sales = 
VAR NumberOfDays = 30
VAR LastCurrentDate = MAX ( 'Date'[Date] )
VAR Period =
    DATESBETWEEN (
        'Date'[Date],
        LastCurrentDate - NumberOfDays + 1,
        LastCurrentDate
    )
RETURN
    AVERAGEX (
        Period,
        [Total Sales]
    )

Breaking Down the Formula

This formula may look complicated, but it's built from a few simple parts working together:

• VAR NumberOfDays = 30: We store the number of days for our average (30) in a variable. This makes it easy to change later if you want a 7-day or 90-day average instead.
• VAR LastCurrentDate = MAX('Date'[Date]): In any context (like a cell in a table or a point on a line chart), this captures the last visible date for that specific point.
• VAR Period = DATESBETWEEN(...): This is where the magic happens. DATESBETWEEN creates a temporary table of dates. We tell it to start from 29 days before our LastCurrentDate (i.e., LastCurrentDate - NumberOfDays + 1) and end on the LastCurrentDate. This gives us a rolling window of exactly 30 days.
• AVERAGEX(Period, [Total Sales]): The AVERAGEX function iterates over the table we created in our Period variable (our 30-day window). For each date in that window, it calculates our [Total Sales] measure and then computes the average of those 30 values.

Visualizing Your Moving Average

The best way to see the power of a moving average is with a combo line chart.

1. Click on the Report view icon in Power BI.
2. Select a Line chart from the Visualizations pane and add it to your canvas.
3. From your Date table, drag your Date column to the X-axis on the Visualizations pane.
4. From your Sales table, drag your base measure (Total Sales) and your new moving average measure (30-Day Moving Average Sales) to the Y-axis.

The result is powerful: you'll see a volatile, "spiky" line representing your daily sales, and running right through it, a much smoother line representing the 30-day moving average. This second line reveals the true trend that was hidden by the daily noise.

Advanced Tip: A Dynamic Moving Average Period

What if you want to let your report viewers choose between a 7-day, 30-day, or 90-day average? You can achieve this with a slicer by creating a numeric parameter.

1. Navigate to the Modeling tab in the ribbon.
2. Click New parameter and select Numeric range.
3. Configure the parameter:

Power BI will automatically add a slicer to your report and create a new table called Moving Average Period with a corresponding measure, Moving Average Period Value.

Now, simply update your DAX measure to use the value from this slicer instead of the hardcoded number:

Dynamic Moving Average Sales = 
VAR NumberOfDays = [Moving Average Period Value]
VAR LastCurrentDate = MAX ( 'Date'[Date] )
VAR Period =
    DATESBETWEEN (
        'Date'[Date],
        LastCurrentDate - NumberOfDays + 1,
        LastCurrentDate
    )
RETURN
    AVERAGEX (
        Period,
        [Total Sales]
    )

Now, your users can slide the slicer on the report page to instantly change the calculation window, switching between short-term and long-term trends on the fly.

Final Thoughts

Calculating a moving average in Power BI is a fundamental technique for turning complex, noisy data into a clear story about your business performance. By setting up a dedicated date table and using a straightforward DAX pattern with AVERAGEX and DATESBETWEEN, you can easily add this powerful analysis to your reports and reveal the long-term trends that truly matter.

While mastering DAX is a fantastic skill, we know it can sometimes feel like a roadblock when you just need a quick answer. We made Graphed to remove that friction. Instead of writing formulas, you can connect your data sources and simply ask, "What's my 30-day moving average of sales from Google Analytics?" Graphed builds the live, interactive visualization for you, converting hours of report building into a 30-second conversation so you can get an answer and get back to growing the business.