Moving Averages (Basic)

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.
Moving Average Period Options

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
5 Likes

Just adding some keywords to make this easier to locate via forum search:
DAX pattern, moving, rolling, average, AVERAGEX

Related Content:

Below are a few examples of issues related to the basic Moving Averages DAX Pattern from the eDNA resources.

Enterprise DNA Forum (Search):

Enterprise DNA Forum (EDNA):

Enterprise DNA TV (YouTube):

Calculate A Monthly Moving Average Year To Date (YTD) in Power BI Using DAX

Calculate A Rolling Average In Power BI Using DAX

Showcasing Trends Using Moving Average Techniques In Power BI

Re-Using Moving Averages - Power BI & DAX Trend Analysis Technique

1 Like