Moving average in PowerBI: How to gain clear insight from fluctuating data
If you work with monthly figures, weekly results, or other time series, you will recognize this. One period shoots up, the next drops, and before you know it, you can no longer see the trend through the fluctuations. A moving average helps you bring calm to the data and reveal the real pattern. In this blog, I will take you step by step through the logic, the calculations, and the visual. I will also show you how to apply the moving average in a margin dashboard.
What can you do with the moving average?
A moving average is ideal when your data fluctuates significantly from month to month and you want to understand where the whole is heading. Think of revenue that varies greatly from month to month, costs that peak due to a one-off invoice, or lead inflow that varies from week to week. By taking an average over, say, six months, you get a much more stable picture. This allows you to see not only what is happening, but also whether there is growth, stabilization, or decline. It makes trend analysis more reliable and helps you make better decisions.
Clearly recognize trends
Ignore peaks and troughs and focus on the longer-term trend.
Strengthen signals for decision-making
A moving average makes it easier to compare periods.
How to apply the moving average
The visual consists of two parts.
- Part 1: The moving average in a line chart.
- Part 2: The min, max, and bandwidth.
Below, we will take you step by step through the process of creating this visualization. You can choose to keep the video below the step-by-step plan open, where I demonstrate how to carry out the step-by-step plan.
Download the Power BI file for the assignments here:
1. Click on the link
2. Go to “file”
3. Click on “Download this file”
4. Click on “A copy of your report and data (.pbix)”
5. The file has now been downloaded
Part 1: The moving average in a line chart
1. The line visual
> Place the line visual in the square provided for this purpose
> Place Year + Month short in the X-axis
> Place the selection calculation in the Y-axis For more options, select Sort axis ascending by year name short
*Select the formatting options as desired
2. The moving average
Go to “New data visualization calculation.”
Click on “Moving average.”
Change the formula:
Field > Choice calculation
Window size > Number of months of moving average
3. Format moving average
Go to “Format visual” to “Lines.” Select the “Moving average” line and choose
> Line style: dotted
> Width: 2
> Color: choose yourself
> Go to “Format visual” to “Shade area” Select the moving average line and disable it
4. > Moving average
Go to “New data visualization calculation.”
Click on “Custom.”
Process here:
ACT > MA 6M = IF([Selection measurement] > [Moving average], [Selection measurement])
5. Format > Moving average
Go to “Format visual” and select “Lines.”
Disable the line “> Moving average.”
Go to “Format visual” and select “Give area a dark color.”
Select the line “> Moving average” and disable it.
6. Markings for months above the moving average
Go to “Format Visual” and then “Markings.” Enable the markings for “> Moving average.” If desired, the shape, size, and color can be changed.
Part 2: The min, max, and bandwidth
1. The line visual
Copy the line visual we just created in section 1 and paste it into the empty box
2. The MIN function
Go to “New data visualization calculation”
Click on “Custom”
Enter the following:
MIN =
EXPANDALL(
MINX(ROWS, [Moving average 1]),
ROWS )
3. The MAX function
Go to “New data visualization calculation”
Click on “Custom”
Enter the following:
MAX =
EXPANDALL( MAXX(ROWS, [Moving average 1]),
ROWS )
4. Formatting the MIN & MAX function
Go to “Format visual” and select “Lines.”
Select the MIN & MAX lines and disable them.
5. Bandwidth bar
Go to “Add further analyses to your visual.”
Click on “Add rule” and add:
MAX: value = MAX
MIN: value = MIN
Select the following for both constants:
Color: light gray
Transparency% = 100%
Position: Behind
Select the MIN constant and:
enable give area a dark tint
Position: Front
Color black
Transparency% = 0%
Select the MAX constant and:
switch on give area a dark tint in
Position: Front
Color: light gray
Transparency% = 80%
Application of moving averages in practice
In dashboards that are frequently used by management, you will see that the moving average often changes the conversation. Instead of responding to random peaks in a single month, you look at structural movement.
Think of sales dashboards where seasonal influences play a significant role. With a moving average, you can immediately see whether an increase is real growth or just a normal seasonal peak.
It also works well in operational dashboards. For example, when monitoring service tickets that sometimes increase in a single week due to a release. A moving average shows whether the workload is really growing or whether it is a temporary disruption.
Using this element consistently in your dashboards creates a much calmer and more reliable picture of your data, with insights that are immediately usable.
Learn more about line charts in Power BI
A moving average is most powerful when it is used in the right type of line chart. The way your data is displayed has a direct impact on how trends are perceived and interpreted.
In the module Line Chart Variants in Power BI I explain the differences between line charts, area charts, stacked area charts and 100% stacked area charts. You’ll learn when each variant adds clarity and when it can actually distort the story. This helps you understand not just how to calculate a moving average, but also when it truly adds analytical value.
