Calculating Moving Averages in Power BI is an essential component of analysis. They’re easy to write using DAX formulas or you can use AI to write them for you with Power BI Copilot. We’ll explain the steps needed for simple moving average calculations and other items that are good to know.
Moving averages are extremely useful for smoothing out trend data that has a natural fluctuation to it. It’s an extremely common technique within financial analysis to identify various trends and how they compare to short term and long-term trends.
We’ll look at multiple ways to calculate moving averages in Power BI.
What is a Moving Average?
A moving average is a statistical technique used to analyze data points. It is typically used to smooth out short-term fluctuations and highlight longer-term trends Moving averages are prevalent in forecasting, stock market analysis, and various kinds of trend analyses. They calculate the average of a value over a specified period of time and can range from few hours, days, months or years depending on the time series of the data that you’re analyzing.
Here are some examples where you might calculate a moving average.
- Smoothing Data – Time-series data with short-term volatility that you wish to smooth out to identify an underlying trend.
- Trend Analysis – Analyzing data to find the general direction in which your data is moving over time.
- Seasonality – Removing the effects of seasonality in your data.
Let’s see how to calculate them!
How To Calculate a Moving Average
There are two common types of moving average calculations, a Simple Moving Average (SMA) and Weighted or Exponential Moving Averages (EMA). The Simple Moving Average is much more common, and easier to calculate. It equally weights all periods in a time series the same, where-as an Exponential or Weighted Moving Average overweights the most recent time periods compared to the oldest ones.
In our article we will focus on calculating the Simple Moving Average, as the math for Exponential weighted averages becomes much more complicated and is not quickly created in Power BI without additional setup.
The formula for a simple moving average is
Total of All Amounts in the overall time period divided by the number of time periods.
We will use this same basic formula in creating our Power BI DAX formulas. But before we create our DAX formulas Power BI needs to have a date table available for it to properly calculate the number of periods in a time-series.
Creating a Date Table in Power BI (Pre-Requisite)
Before calculating moving averages, it’s essential to have a sorted date table. This ensures that your calculations are performed in chronological order. The fastest way to create a date table in Power BI is using the CALENDARAUTO DAX function.
Navigate to the Table Tools section of the Power BI ribbon and click on New Table. A formula bar will appear allowing you to type the name of the new table and the formula CALENDARAUTO(12).
The 12 in the formula is to designate the month end date. You can adjust this if needed, but December is the most common end date to use.
After creating a date table, you will want to define it as the date table by right clicking it in the field list and selecting date table. You will also want to make sure that a relationship is built between the existing date column in your time-series data and the new date table.
There is actually a lot of nuance and detail to building date tables that is good to know. While CALENDARAUTO is a good start, we published an extensive guide on how to create date tables in Power BI that is a good read to help working with dates and sorting.
Whenever you reference a date when creating a DAX formula for running totals, you will want to reference the date column on the newly created date table.
How to Calculate a Moving Average in Power BI with DAX
The easiest way to calculate a moving or rolling average in Power BI is to use a combination of the AVERAGEX formula and the DATESINPERIOD DAX formula. The DATESINPERIOD formula will dynamically calculate the number of days in between the beginning and end dates. The AVERAGEX formula will automatically use the number of dates to calculate an average.
You have to make a dynamic formula so Power BI knows when to begin and end the calculation. The following example formula uses a field called Sales on a Sales table, and a field called Date on a Date Table.
The following is the calculation setup as a 7 day moving average that you can copy and paste into your own Power BI Dashboard. You will have to update the table and field references to match your own data.
7 Day Moving Average =
CALCULATE(
AVERAGEX('Sales',Sales[Sales]),
DATESINPERIOD(
'Date Table'[Date],
LASTDATE('Date Table'[Date]),
-7,
DAY
)
)
The CALCULATE function in Power BI DAX tells Power BI that you are modifying filter context. It’s necessary for the formula to calculate a custom date range and to work correctly when using filters or slicers on the page.
Let’s look at another faster and quick method to create moving averages!
How to Calculate a Moving Average with Power BI Quick Measures
Quick measures in Power BI are pre-made measure templates. They let you drag and drop fields into a template, and Power BI generates the DAX formula on your behalf. Navigate to Table Tools > Quick Measure.
A panel will appear on the right side of the screen with a dropdown menu that lets you select the type of calculation you want to perform. Simple Moving Averages and Rolling Averages are the same thing. Select Rolling Average from the Dropdown list.
Fill out the Quick Measure wizard on the right. The Base Value is the amount that you are summing or calculating the average of. The Date field needs to be the date field from the newly created date table. If the date column you are using is not on a specified date table, Power BI will produce an error asking you to update it.
The final section is to define the periods that you want the formula for, days, months, or years. Then there are additional options where you can calculate a trailing average that only looks back a certain number of periods or you could calculate a future looking rolling average with periods after.
When you click on Add, Power BI will generate the DAX formula in the top formula bar. Update the name of the new measure and it will be saved for use in other parts of your report and dashboard.
How to Calculate a Moving Average in Power BI with Copilot
Suggestions with Copilot is a relatively new addition to Power BI. It uses natural language processing and Artificial Intelligence to translate a description of the measure that you want into DAX code. It’s available under the Quick Measure section, but instead of the Calculations tab, click on Suggestions with Copilot.
In the example below, we asked Power BI to create a moving average of the sales column for the past 7 days. When we wrote it, we were somewhat lazy and typed it in like we would type a request into Google, but the results are a working formula for a rolling 7 day average.
If you don’t have Power BI Copilot available, or it is greyed out there may be some setup required. We put together a Guide to Enable Power BI Copilot that you should take a look at. Some options require Power BI Admin support to turn on specific options if they’re not already enabled, but it’s a really cool tool and extremely helpful for building DAX formulas.
Adding Moving Averages to Power BI Line Charts
To add a moving average calculation to Power BI visuals, you have to first calculate them using a DAX formula. Once the DAX formula is created, they can be added to the values section of the required visual. While Power BI does have a number of options to analyze time-series data in a line chart, such as adding average lines, forecasts, regression trends, and anomaly detection there is no single option to auto-calculate a moving average.
One benefit of this approach is that you can add multiple measures for moving averages with multiple time-series defined. Below is an example of a line chart with multiple moving averages added along with a regression line to aid in analysis of performance of short term compared to long term trend.
Power BI is a powerful analysis tool and gives people lots of granularity when creating visualizations. Let’s take a look at what a different type of moving average calculation, the exponential moving average.
Performing Analysis using Moving Averages on Charts
One of the biggest use cases for moving averages is technical analysis of financial markets. Trend lines are analyzed to identify shifts in momentum and trends that can help stock traders make money. The level of detail and amount of resources they have available far exceeds what is commonly setup in Power BI. However, we want to point out the following video to give you some ideas of how you might be able to use Rolling Averages in your own Business Analysis.
Conclusion
Moving averages are an invaluable tool for trend analysis and smoothing short term volatility in your dataset. Power BI offers a robust set of features, making the calculation and visualization of moving averages straightforward and effective. Whether you are using DAX, Quick Measures, or AI powered Copilot, mastering moving averages can greatly enhance your analytical capabilities.