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: