Calculate the average of the first 3 months from last 6 months

[pmargari]

Hello
I'm tring to calculate the average of the first 3 months going back 6 months , in the below example and if we pick January
I need to return 6 months and from that period I need to calculate the average of the fist 3 months of this period of 6 months
So for January I need the average of (July,  August and September) , etc as below

Any tips ?
Appreciated

[johnt75 · Accepted solution]

You could use the WINDOW function, something like

Avg first 3 months =
VAR DatesToUse =
    WINDOW (
        -6,
        REL,
        -4,
        REL,
        ALL ( 'Date'[Year month], 'Date'[Year month sort column] ),
        ORDERBY ( 'Date'[Year month sort column] )
    )
VAR Result =
    AVERAGEX (
        DatesToUse,
        CALCULATE (
            [Measure to average],
            ALLEXCEPT ( 'Date', 'Date'[Year month], 'Date'[Year month sort column] )
        )
    )
RETURN
    Result

[pmargari]

Hello johnt75

It works , thanks a lot for your tip !!

Avg first 3 months =

VAR DatesToUse =
WINDOW (
-6,
REL,
-4,
REL,
ALL ( 'Date'[YearMonth], 'Date'[Year-Month Index]),
ORDERBY ( 'Date'[Year-Month Index])
)
VAR Result =
AVERAGEX (
DatesToUse,
CALCULATE (
DIVIDE([# Leads],[# Processos]),
ALLEXCEPT ( 'Date', 'Date'[YearMonth], 'Date'[Year-Month Index] )
)
)
RETURN
    Result

PREVIEW

[pmargari]

Thanks for feedback and I'm going to test !!