Question

How can I calculate a moving average with a variable period in DAX?
I need to create a DAX measure that calculates a moving average over a variable period, allowing users to define the window size dynamically. The measure should adjust based on user selections and maintain accuracy across different date ranges. What is the best approach using DAX functions like AVERAGEX, FILTER, or DATESINPERIOD to ensure flexibility and correct calculations?

Answer 1

Moving average computation with variable periods in DAX would require a measure that adjusts dynamically according to the user's selection of computed values for different ranges created by dates. To achieve this, you could use a combination of AVERAGEX, FILTER, and DATESINPERIOD to create a dynamic moving average measure.

Best Approach Using DAX

Design a Parameter Table for Moving Average Window

Create a table called MovingAvgPeriod, having values like 7, 14, and 30, and give users the possibility to choose this period.

Add this as a disconnected table (with no relationships).

Use a slicer to let the user select the window's size.

Define the Dynamic Moving Average Measure

MovingAvg = 
VAR SelectedPeriod = SELECTEDVALUE(MovingAvgPeriod[Days], 7) 
RETURN 
AVERAGEX(
    DATESINPERIOD(Sales[Date], MAX(Sales[Date]), -SelectedPeriod, DAY), 
    CALCULATE(SUM(Sales[Amount]))
)

Ensure Flexibility and Accuracy

• DATESINPERIOD dynamically selects the date range based on the user-defined window.

• MAX(Sales[Date]) ensures the moving average is calculated for the most recent date in context.

• CALCULATE(SUM(Sales[Amount])) ensures proper aggregation within the selected period.