Forum Discussion

Jace920's avatar
Jace920
Regular Visitor
1 year ago
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...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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.

     

     

lbendlin's avatar
lbendlin
Super User
1 year ago

This can be simplified a bit.

 

 

 

You can then add logic to match the hire IDs to the termination IDs if needed.

 

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

 

Jace920's avatar
Jace920
Regular Visitor
1 year ago

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.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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.

     

     

    • Jace920's avatar
      Jace920
      Regular Visitor
      1 year ago

      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.

    • Jace920's avatar
      Jace920
      Regular Visitor
      1 year ago

      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!