DAX coding

Hello !

Thank you for posting on Microsoft Fabric community.

You don’t need all those year specific hacks.

You can make one measure that always pulls the last N month-ends (2 for Month, 4 for Quarter, 13 for Year) which naturally crosses into the prior year for Jan/Q1/Full Year.

-- FTE at month-end for the current filter context
FTE EOM =
VAR EOMD =
    EOMONTH ( MAX ( Periods[Period (dt)] ), 0 )
RETURN
    CALCULATE ( [FTEs], KEEPFILTERS ( Periods[Period (dt)] = EOMD ) )

-- Main: Avg FTE for Month / Quarter / Year (includes prior month/quarter-end/Dec PY)
Avg FTE =
VAR IsMonth   = ISINSCOPE ( Periods[Month Name] )
VAR IsQuarter = ISINSCOPE ( Periods[Qtr] )
VAR IsYear    = ISINSCOPE ( Periods[Year] ) && NOT IsQuarter && NOT IsMonth

-- Use the last visible month-end in the current context as the anchor
VAR AnchorEOM = EOMONTH ( MAX ( Periods[Period (dt)] ), 0 )

-- How many months back (besides the anchor month) do we include?
-- Month: 1 prior (=> 2 months total)
-- Quarter: 3 prior (=> 4 months total; adds last month of previous quarter)
-- Year: 12 prior (=> 13 months total; adds Dec of prior year)
VAR MonthsBack =
    SWITCH (
        TRUE (),
        IsMonth,   1,
        IsQuarter, 3,
        IsYear,   12,
        /* default */ 1
    )

-- Build the list of month-end dates to average over
VAR MonthEndsTableRaw =
    SELECTCOLUMNS (
        DATESINPERIOD ( Periods[Period (dt)], AnchorEOM, -MonthsBack, MONTH ),
        "EOM", EOMONTH ( Periods[Period (dt)], 0 )
    )
VAR MonthEnds =
    DISTINCT ( MonthEndsTableRaw )  -- one row per month-end

-- Average the FTE month-ends across the window
RETURN
AVERAGEX (
    MonthEnds,
    VAR d = [EOM]
    RETURN CALCULATE ( [FTEs], KEEPFILTERS ( Periods[Period (dt)] = d ) )
)

If you must force prior-year month-ends to actuals only you can wrap the inner CALCULATE with a conditional filter.

RETURN
AVERAGEX (
    MonthEnds,
    VAR d = [EOM]
    VAR anchorYear = YEAR ( AnchorEOM )
    VAR y = YEAR ( d )
    RETURN
        IF (
            y < anchorYear,
            CALCULATE (
                [FTEs],
                KEEPFILTERS ( Periods[Period (dt)] = d ),
                KEEPFILTERS ( Master_Data[Submission] IN { "PY_ACTUAL" } || Master_Data[SCENARIO] IN { "ACTUAL" } )
            ),
            CALCULATE ( [FTEs], KEEPFILTERS ( Periods[Period (dt)] = d ) )
        )
)