MOVING AVERAGE LAST 3 QUARTER O LAST N QUARTER

[mmunozi]

Im trying to build a moving average of sales for the last three quarters (or *n* quarters) in a way that the measure can be reused for any number of quarters. After a lot of effort, I managed to create a measure that calculates the three-quarter average, but I find it too long and hard to maintain. Could you help me build a more flexible measure that works for *n* quarters?  Any guidance you can share would be incredibly helpful

this is my Measures I think is too large and is very difficult to maintain. I need to calculte Ventas for 4 o 5 o N last quarter .

PROMEDIO MÓVIL VENTAS 3 TRIMESTRES = 
VAR _QuarterActual =
    MAX ( Calendario[QuarterSeq] )

// Quarters to consider (last 3 including the current one)
VAR _Trimestres = // Defines the 3-quarter window using QuarterSeq
    FILTER (
        ALL ( Calendario ),
        Calendario[QuarterSeq] >= _QuarterActual - 2 && // Current - 2 (third quarter back)
        Calendario[QuarterSeq] <= _QuarterActual      // Current Quarter
    )

// Total sales in the last 3 quarters
VAR TOTAL_ACUMULADO_VENTAS =
    CALCULATE ( [VENTAS], _Trimestres ) // Sums sales in the defined window

// Count how many quarters (of the last 3) have sales
VAR N_TRIMESTRES = // Counts quarters with sales activity within the calendar window
    CALCULATE (
        DISTINCTCOUNT ( Calendario[Trimestre] ), // Counts distinct quarter names (e.g., "Q1", "Q2")
        FILTER (
            ALL ( Calendario ),
            CALCULATE ( COUNTROWS ( 'Order Details' ) ) > 0 // Condition: Only considers days/rows from the calendar with data in 'Order Details'
        ),
        DATESINPERIOD ( // Filters the calendar to the last 3 quarters based on dates
            Calendario[Date],
            MAX ( Calendario[Date] ), // Last date in the current context
            -3, // 3 quarters back
            QUARTER
        )
    )

// Return average: divide total by the actual count of quarters with sales (maximum 3)
RETURN
    DIVIDE (
        TOTAL_ACUMULADO_VENTAS,
        IF ( N_TRIMESTRES > 3, 3, N_TRIMESTRES ) // Denominator: uses the N_TRIMESTRES count, ensuring it's not > 3
    )

This is my_ model.PBI 

[rajendraongole1 · Accepted solution]

Hi @mmunozi  - create a reusable and dynamic measure that calculates the moving average for the last n quarters, we can parameterize the number of quarters and simplify the logic.

PROMEDIO_MOVIL_VENTAS_N_TRIMESTRES =
VAR N = 3 -- 👈 Change this value for different window sizes
VAR CurrentQuarter = MAX(Calendario[QuarterSeq])
VAR QuarterWindow =
FILTER (
ALL ( Calendario ),
Calendario[QuarterSeq] <= CurrentQuarter &&
Calendario[QuarterSeq] > CurrentQuarter - N
)
VAR TotalSales =
CALCULATE ( [VENTAS], QuarterWindow )

VAR QuarterCount =
CALCULATE (
DISTINCTCOUNT ( Calendario[QuarterSeq] ),
FILTER (
QuarterWindow,
CALCULATE ( COUNTROWS ( 'Order Details' ) ) > 0
)
)
RETURN
DIVIDE ( TotalSales, QuarterCount )

[mmunozi]

Thank you very much! You are Master of DAX