Forum Discussion
Calculating 1st Year Employee Retention Rates
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.
Thanks for the simplified approach. Unfortunately, I'm not getting the sums I expect. I have modified the measure as you described and included the additional logic to match on the specific hires from the year prior. The measure is returning values much smaller than expected.
Revised measure with suggested logic:
TerminatedWithin1Year =
var md = min('Date'[Date])
RETURN CALCULATE(
DISTINCTCOUNT(Terms[Empl_ID]),
Terms[Term Date] in CALENDAR(EDATE(md,-12),md),
TREATAS(
VALUES('Hires'[Empl_ID]),
Terms[Empl_ID]
)
)
For July and August, the total hires matches my expected totals, but the terms do not
Based on the sample data provided, I am expecting 129 first year terms for July and 155 for August, but the measure is returning 18 and 7 respectively.
Thank you.
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.
Thanks for your continued assistance. I'll take a closer look, remove FY references from the visuals to write that off as a possible issue and see what results I come up with.
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!