topic Calculate the average of the first 3 months from last 6 months in DAX Commands and Tips

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

pmargari — Wed, 02 Aug 2023 11:35:00 GMT

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

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

johnt75 — Wed, 02 Aug 2023 13:05:58 GMT

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

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

pmargari — Wed, 02 Aug 2023 13:34:30 GMT

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

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

pmargari — Wed, 02 Aug 2023 14:04:42 GMT

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