Let us consider a data set of sales. The objective is to analyze daily sales volume. If we simply build a report that plots the sales amount sliced by day, the result is difficult to decipher.
Following is the time series of day-wise total sales:
Total Sales = SUM(Orders[Sales])
The graph above shows high variations in total sales value with a horizontal trend pattern.
In statistical data modeling, a common approach is to calculate the average over a certain period higher than just the day level, especially when the time series shows a horizontal pattern over a period. It is also known as Moving Average.
What is a moving average?
The moving averages method uses the average of the most recent k data values in the time series. We call it moving because every time a new observation becomes available for the time series, it replaces the oldest observation in the equation with the latest value and calculates a new average. Thus, the periods over which the average is calculated changes, or moves, with each ensuing period.
Following is an example of 3 periods moving average (k = 3)
Calculating Moving Average using DAX
The objective here is to calculate the moving average of the last 30 days. So, k = 30
Experts recommend creating at least one calendar table in the data model. In the current model, I have created a calendar table and established the relationship:
You may refer to the following article on Calendar Tables.
Then, create a measure with the following code:
Avg Sales (MA 30 days) = //Selecting the date in the range VAR _LastDate = MAX ( dtCalendar[Date] ) //Defining the duration to be considered for average calculation(k) VAR _Duration = 30 //Filtering the Calendar Table for the defined range VAR _CalculationPeriod = FILTER ( ALL ( dtCalendar ), --Removing any external filters context applied AND ( dtCalendar[Date] > _LastDate - _Duration, --the range start date dtCalendar[Date] <= _LastDate --the range end date ) ) //Calculating the Moving Average VAR _MovingAverage = IF ( COUNTROWS ( _CalculationPeriod ) >= _Duration, --Condition to check minimum number of days for the calculation. CALCULATE ( AVERAGEX(dtCalendar,[Total Sales]), --Calculating average of the total sales amount _CalculationPeriod ) ) RETURN _MovingAverage
The formula first determines the last visible date; in the visual, because the filter context set by the visual is at the date level, it returns the selected date (VAR _LastDate)
Then it creates a set of all the dates between the last date and the last date minus 30 days (VAR _CalculationPeriod).
Finally, the last step is to use this period as a filter in CALCULATE, so that the final AVERAGEX iterates over the 30 days, computing the average of the daily sales.
Following graph presents the output: