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.