Forum Discussion
DAX coding help for average FTE calculation
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.
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!