Forum Discussion
Solved
Calculating 1st Year Employee Retention Rates
Greetings community! I've been tasked with calculating the 1 year retention rate of newly hired employees month over month. I've spent a lot of time tweaking my DAX to achieve this, but have been u...
I refactored the formula to make it bulletproof - still am arriving at different numbers.
TerminatedWithin1Year = VAR a = CALCULATETABLE ( Hires, SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) VAR b = ADDCOLUMNS ( a, "termdate", CALCULATE ( MAX ( Terms[Term Date] ), TREATAS ( { [Empl_ID] }, Terms[Empl_ID] ) ) ) RETURN COUNTROWS ( FILTER ( b, COALESCE ( [termdate], dt"2030-01-01" ) < EDATE ( [MostRecentHireDate], 12 ) ) )I guess some of the confusion is around the mismatch between Fiscal and Calendar years.
I refactored the formula to make it bulletproof - still am arriving at different numbers.
TerminatedWithin1Year =
VAR a =
CALCULATETABLE ( Hires, SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR b =
ADDCOLUMNS (
a,
"termdate",
CALCULATE (
MAX ( Terms[Term Date] ),
TREATAS ( { [Empl_ID] }, Terms[Empl_ID] )
)
)
RETURN
COUNTROWS (
FILTER (
b,
COALESCE ( [termdate], dt"2030-01-01" ) < EDATE ( [MostRecentHireDate], 12 )
)
)I guess some of the confusion is around the mismatch between Fiscal and Calendar years.
Turns out the validation file I was using had an incorrect formula in it throwing off the expected numbers slightly. This 'bulletproof' approach is achieving what I needed. Thanks for your assistance!