Average calculation of FTE with switch of YTD and MTD

[mariomyhanh]

Hello , I need some help please. I'm struggling to wrap my head around some DAX needed to calculate the average FTE YTD depending on the month selection

I have a selection table with value column of:

current period

YTD

 and data set 

period	FTE Actuals	would like to return value for YTD
sept	60	60
oct	43.83	51.92
nov	44.13	49.32
dec	43.27	47.80
Jan	40.25	46.29

my current measure for current month is correct, but the YTD is returning the sum or total of all months and not the average.

MTD YTD Switch =

switch(

    True(),

    SELECTEDVALUE(SelectionTable[Value]) = "Current Period", [FTE-Actuals],

    SELECTEDVALUE(SelectionTable[Value])="YTD",TOTALYTD([FTE-Actuals],'Calendar'[Date],"8/31"),

    [FTE-Actuals]

    )

[Anonymous]

Hi @mariomyhanh ,

Please try this code to create a measure.

MTD YTD Switch =
VAR _SELECTVALUE =
    SELECTEDVALUE ( SelectionTable[Value] )
VAR _MONTH =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _MONTH_COUNT =
    IF ( _MONTH > 8, _MONTH - 8, _MONTH + 12 - 8 )
VAR _YTD =
    TOTALYTD ( SUM ( 'Table'[FTE Actuals] ), 'Calendar'[Date], "8/31" )
RETURN
    SWITCH (
        TRUE (),
        _SELECTVALUE = "Current Period", [FTE-Actuals],
        _SELECTVALUE = "YTD", DIVIDE ( _YTD, _MONTH_COUNT ),
        [FTE-Actuals]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mariomyhanh]

Good morning, would you mind explaining what this mean please, the code works, but would like to understand the section below for future use.  Thank you

MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _MONTH_COUNT =
    IF ( _MONTH > 8, _MONTH - 8, _MONTH + 12 - 8 )

[mariomyhanh · Accepted solution]

Thank you so much!