DAX coding

[psstack]

Hi All,

I have been tryiong to develop a measure that will calculate the avgFTE over a selected period of time, but have not been able to get it to calculate correctly.  The code I have works for months Feb-Dec and Q2-Q4, but does not work for Jan, Q1 or Full year averages where the calculation has to span to the prior year.  The logic for calculating avg FTE for a specific month is the average of the ending FTE for that month and the prior month.  For the qtr it is the average of the ending FTEs for the 3 months of the selected qtr and the last month of the prior qtr.  And for full year it is the average of the ending FTE for each month of the selected year and Dec of the prior year.   

Below is the code I have written as well as a snippet from my dashboard.  Can anyone help in fixing this code or writing a new code that would work for this calculation? 

Avg_FTE =

VAR sQtr =

    SELECTEDVALUE ( Periods[Qtr] )

VAR sMonth =

    SELECTEDVALUE ( Periods[Month Name] )

VAR sYear =

    SELECTEDVALUE ( Periods[Year] )

VAR sDate =

    MAX ( Periods[Period (dt)] )

VAR sEndMonth =

    EOMONTH ( sDate, 0 )

VAR maxYear =

    CALCULATE ( MAX ( Periods[Year] ), REMOVEFILTERS ( Periods ) )

VAR maxDate =

    CALCULATE ( MAX ( Periods[Period (dt)] ), REMOVEFILTERS ( Periods ) )

VAR NewEndofMonth =

    SWITCH (

        TRUE (),

        sYear

            == maxYear && ISBLANK ( sMonth )

                && ISBLANK ( sQtr ), maxDate,

        sYear

            == maxYear && ISBLANK ( sMonth )

                && sQtr = "Q4", maxDate,

        sYear

            == maxYear

                && NOT ( ISBLANK ( sMonth ) )

                    && sMonth = "Dec", maxDate,

        sEndMonth

    )

VAR sQtrCal =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -4, MONTH ),  

        REMOVEFILTERS ( Periods )

    )

VAR sYearCal =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -13, MONTH ),  //Master_Data[SCENARIO] in {"ACTUAL", "M5_Forecast"},

        REMOVEFILTERS ( Periods )

    )

VAR sMonthCal =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -2, MONTH ),  //Master_Data[SCENARIO] in {"ACTUAL", "M5_Forecast"},

        REMOVEFILTERS ( Periods )

    )

VAR FirstMonthCalcPY =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -2, MONTH ),  Master_Data[Submission] in {"PY_ACTUAL"} || Master_Data[SCENARIO] in {"ACTUAL"},

        REMOVEFILTERS ( Periods )

    )

VAR FirstMonthCalcPY_Qtr =

    CALCULATE (

        [FTEs],

        //Periods[Period] = "202412",Master_Data[SCENARIO] in {"ACTUAL"}    

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -4, MONTH ),  Master_Data[Submission] in {"PY_ACTUAL"} || Master_Data[SCENARIO] in {"ACTUAL"},

        REMOVEFILTERS ( Periods )

    )

VAR LastMonthPY =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -13, MONTH ),  Master_Data[Submission] in {"PY_ACTUAL"} || Master_Data[SCENARIO] in {"ACTUAL"},

        REMOVEFILTERS ( Periods )

    )

VAR FirstMonthCalcCY =

    CALCULATE (

        [FTEs],

        DATESINPERIOD ( Periods[Period (dt)], NewEndofMonth, -2, MONTH ),

        REMOVEFILTERS ( Periods )

    )

RETURN

    SWITCH (

        TRUE (),

        ISBLANK ( sMonth ) && ISBLANK ( sQtr ) && sYear= 2025, DIVIDE (LastMonthPY + sYearCal, 13, 0 ),

        ISBLANK ( sMonth ) && ISBLANK ( sQtr ), DIVIDE ( sYearCal, 13, 0 ),

        ISBLANK ( sMonth ) && sQtr = "Q1" && sYear = 2025, DIVIDE(FirstMonthCalcPY_Qtr+sQtrCal,4,0),

        ISBLANK ( sMonth ), DIVIDE ( sQtrCal, 4, 0 ),

        sMonth = "Jan" && sYear = 2025, DIVIDE(FirstMonthCalcPY+FirstMonthCalcCY,2,0),

        DIVIDE ( sMonthCal, 2, 0 )

    )

[v-hashadapu]

Hi @psstack , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @AmiraBedh  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you @AmiraBedh  for your valuable response..

[v-hashadapu]

Hi @psstack , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @psstack , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Shahid12523]

Use this

Avg_FTE =
VAR sDate = MAX(Periods[Period (dt)])
VAR sMonth = SELECTEDVALUE(Periods[Month Name])
VAR sQtr = SELECTEDVALUE(Periods[Qtr])
VAR sYear = SELECTEDVALUE(Periods[Year])
VAR EndOfMonth = EOMONTH(sDate, 0)

-- Prior Month
VAR PriorMonthDate = EOMONTH(EndOfMonth, -1)
VAR PriorMonthFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] = PriorMonthDate
)

-- Current Month
VAR CurrentMonthFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] = EndOfMonth
)

-- Quarter Dates
VAR QuarterDates =
DATESINPERIOD(Periods[Period (dt)], EndOfMonth, -3, MONTH)
VAR PriorQtrMonthDate = EOMONTH(EndOfMonth, -3)
VAR QtrFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] IN QuarterDates
)
VAR PriorQtrFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] = PriorQtrMonthDate
)

-- Year Dates
VAR YearDates =
DATESINPERIOD(Periods[Period (dt)], EndOfMonth, -12, MONTH)
VAR PriorYearDecDate = DATE(sYear - 1, 12, 31)
VAR YearFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] IN YearDates
)
VAR PriorYearDecFTE =
CALCULATE(
[FTEs],
Periods[Period (dt)] = PriorYearDecDate
)

RETURN
SWITCH(
TRUE(),
-- Full Year
ISBLANK(sMonth) && ISBLANK(sQtr),
DIVIDE(YearFTE + PriorYearDecFTE, 13),

-- Q1
ISBLANK(sMonth) && sQtr = "Q1",
DIVIDE(QtrFTE + PriorQtrFTE, 4),

-- Other Quarters
ISBLANK(sMonth),
DIVIDE(QtrFTE + PriorQtrFTE, 4),

-- January
sMonth = "Jan",
DIVIDE(CurrentMonthFTE + PriorMonthFTE, 2),

-- Other Months
DIVIDE(CurrentMonthFTE + PriorMonthFTE, 2)
)

[AmiraBedh · Accepted solution]

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 ) )
        )
)