Use the Moving Averages pattern and the AVERAGEX iterator to get the average of a measure over the past number of intervals of the desired period type.
For example, to calculate the average sales over the (moving) prior 1 month period:
Sales 1M MA =
// DAX PATTERN NAME: Moving Average
// NOTES: Use the AVERAGEX iterator to get the average of the measure over the past number of intervals of the desired type
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR NumberOfIntervals = -1 // replace "-1" with the desired direction and number of intervals
RETURN
AVERAGEX(
DATESINPERIOD( Dates[Date], // replace Dates[Date] with the name of the main [Date] field in the [Dates] table
LASTDATE( Dates[Date] ), // replace Dates[Date] with the name of the main [Date] field in the [Dates] table
NumberOfIntervals, MONTH ),
[Total Sales] // replace [Total Sales] with the name of the [Total] measure
)
The options for the period type are DAY, MONTH, QUARTER, or YEAR.
NOTE: This is the basic pattern for moving averages and assumes both that enough data is available to generate an accurate average and that there is a full set of contiguous dates; a more complete treatment of real-world scenarios is left for another post, but here’s a reference for further review.
Tips:
To replace all occurrences of a text string = select one occurrence, use CRTL + SHIFT + L, type the replacement text
To zoom the text size = use CRTL + mouse scroll wheel
