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

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.

Outputs:

The Trend of Net Sales and Average Sales is as per below :

The Average Sales trend does not make sense when compare with Sales.

The Trend of Net Sales and 3 Month Moving  Average Sales is as per below :

The  3 Month Moving Average Sales trend is make sense when compare with Sales.

Note:

The red dotted line is the Average line (based on Net Sales) added from Analytics Section of the Line Chart. 

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------