Calculate Moving Average in Power BI

Posted on |

Althroght Power BI provided a quick measure to calculate moving average, it suits filters badly and might have a strange tail after the time range in the graph. Here are 2 different method to create moving average (or whatever).

Create a Date Column

Before the real calculation, it is necessary to create a new column with Year-Month-Quarter-Day breakdown structure. If there is a datetime column in you table already, you can simply create it by:

1
YearMonth = Date(Year([Date]), Month([Date]), Day([Date]))

Where [Date] is the name of your datetime column. It is because the datetime column from Excel (or any other data source) doesn’t contain the breakdown, and we need to refer that structure to Power BI.

Now we have the [YearMonth] (feel free to change the name of it) and we can start the calculation.

Moving Average within Filtered Time Range

If you want to create a moving average which recalculate result when time filter changed, try following code:

1
2
3
4
5
6
7
8
9
10
Moving Avg = (
CALCULATE(
    AVERAGE('Table Name'[Availability]),   
    DATESINPERIOD(
        'Table Name'[YearMonth].[Date], 
        LASTDATE('Table Name'[YearMonth]),
        -12, 
        MONTH) 
        ) 
    )

This code block calculates the moving average of Availability last 12 months. You can change -12 to any number you want, and MONTh can be replaced by YEAR, QUARTER and DAY. Also Simply replace 'Table Name' to your table’s name, [Availability] to the column name which you want to do the calculation, and [YearMonth] likewise.

The visualization and the data looks like this.
Imgur

Global Moving Average

If you don’t want to recalculate the value while changing the filter, simply use ALLEXCEPT function. It allows the columns exclude the filter except the column you want to apply. The following code shows how it works:

1
2
3
4
5
6
7
8
9
10
11
Moving Avg Global = (
CALCULATE(
    AVERAGE('Safety Events'[Accidents]),   
    ALLEXCEPT('Safety Events', 'Safety Events'[Comment]),
    DATESINPERIOD(
        'Safety Events'[YearMonth].[Date], 
        LASTDATE('Safety Events'[YearMonth]),
        -12, 
        MONTH)
        )
    )

The visualization and the data looks like this :
Imgur