How to Calculate the 3 Months Rolling Average Sales in Power BI | Power BI Blog

How to Calculate the 3 Months Moving Average Sales in Power BI

A Rolling Average also known as Moving Average, is a metric that calculates the averages to analyze data for specific time series and to spot trends in that data.

It really useful to identify the exact trend of data when there is a lot of fluctuations in the data.

It can also helps Business to determine the factors that effects the trends.

For example, the 3 Month basis Rolling Average of Sales at Mar-2017 would be calculated by adding the Sales in January, February, and March, and then Dividing that Sum by 3 (the no. of Rolling Months).

Scenario:

Suppose we have a Data Model as per below.

The relationships are as per below:

Now based on the above Model, we can calculate the 3 Month Rolling or Moving Average as per below:

Net Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

Total Orders = COUNT(tbl_OrderDetails[Order_Id])

Avg Sales = AVERAGEX(tbl_Sales, [Net Sales])

Moving Avg Sales = 
VAR MaxSaleDate = MAX(tbl_OrderDetails[Order_Date])
VAR vMinDate = MIN(DimCalendar[DimDate])
VAR vNumOfMonths = SELECTEDVALUE(RollingAvgByMonths[RollingMonths],1)
VAR vMaxSelectDate = MAX(DimCalendar[DimDate])
VAR vMovingPeriod = 
        DATESINPERIOD(DimCalendar[DimDate],vMaxSelectDate,-vNumOfMonths, MONTH)
VAR vMovingAvg = 
    CALCULATE(
            AVERAGEX(
                VALUES(DimCalendar[MonthName]),
                [Net Sales]
                ),
            vMovingPeriod
            )
RETURN
    IF( vMinDate <= MaxSaleDate , vMovingAvg )

Result :

Notes :

For Jan-2022, we don’t have the Prior 2 Months Sales, hence it considered a Moving Average of that one Month.

For Feb – 2022, it has only sales for Jan in the prior Months, hence a Moving Average is taken based on 2 Months as (Jan+Feb) / 2.

For Mar – 2022, it has only sales for Jan, Feb in the prior Months, hence a Moving Average is taken based on 3 Months as (Jan+Feb+Mar) / 3.

If we look at the Jan-2023, it has the sales for prior months Nov-2022, Dec-2022, hence the Average is taken based on 3 Months as (Nov-2022+Dec-2022+Jan-2023) / 3.

RollingMonths :

The slicer “Rolling Months” is created from a Custom Table, which is not related to the Model.