![bookkeeping-615384_960_720.jpg](https://quantinsightsnetwork.com/wp-content/uploads/2023/11/bookkeeping-615384_960_720-678x381.jpg)
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:
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 :
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.
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 3 Month Moving Average Sales trend is make sense when compare with Sales.
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment