DAX coding help for average FTE calculation

[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-saisrao-msft]

Hi @psstack,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @psstack,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @psstack,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @psstack,

Thank you @BeaBF,@FBergamaschi, for your insights.

I’ve reproduced your issue using sample data and confirmed the behavior. The measures are returning the expected results Ending FTE provides the final available value for the period, whereas Avg FTE Month/Quarter/Year shows the appropriate aggregation for each respective level. Ending FTE gives the last available value in the period, while the Avg FTE Month/Quarter/Year reflect the correct aggregation at each level.

Hope this helps

Thank you.

[BeaBF]

@psstack Hi! Try with:

Avg_FTE =
VAR CurrDate = MAX ( Periods[Period (dt)] )
VAR CurrYear = YEAR ( CurrDate )
VAR CurrMonth = MONTH ( CurrDate )

-- Current period FTE (month, quarter, or year context)
VAR CurrFTEs =
CALCULATE ( [FTEs], KEEPFILTERS ( Periods[Period (dt)] ) )

-- Prior month FTE (for Jan, will go to Dec prior year)
VAR PrevMonthFTE =
CALCULATE (
[FTEs],
DATEADD ( Periods[Period (dt)], -1, MONTH )
)

-- Prior quarter last month FTE (for Q1, this will go to Dec prior year)
VAR PrevQtrFTE =
CALCULATE (
[FTEs],
DATEADD ( Periods[Period (dt)], -1, QUARTER )
)

-- Prior year Dec FTE (for Full Year averages)
VAR PrevYearEndFTE =
CALCULATE (
[FTEs],
FILTER ( ALL ( Periods ), Periods[Year] = CurrYear - 1 && Periods[Month Name] = "Dec" )
)

-- Average logic
RETURN
SWITCH (
TRUE(),
-- If at Month granularity
HASONEVALUE ( Periods[Month Name] ),
DIVIDE ( PrevMonthFTE + CurrFTEs, 2 ),

-- If at Quarter granularity
HASONEVALUE ( Periods[Qtr] ),
DIVIDE ( PrevQtrFTE + SUMX ( VALUES ( Periods[Month Name] ), [FTEs] ), 4 ),

-- If at Year granularity
HASONEVALUE ( Periods[Year] ),
DIVIDE ( PrevYearEndFTE + SUMX ( VALUES ( Periods[Month Name] ), [FTEs] ), 13 )
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here