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.
- 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