Calculating 1st Year Employee Retention Rates

[Jace920]

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 unsuccessful to get the Date filter context to play nice.  Hoping someone in the community can identify what I'm doing wrong. 

.pbix and .xlsx that includes example of expected outcome are available on my google drive. 

I have two fact tables and a Date Dimension Table I'm working with.

• Hires - includes all employee ID numbers and their respective MostRecentHireDate
• Terminations - includes all termination transactions.  Data includes employed ID number and Tenure at time of termination
• Date - includes company specific fiscal year identifiers for each date (Fiscal Year is July - June)

My current approach has been:

• For each Fiscal Year and Month, calculate the total number of employees hired using Hires FACT table whose most recent hire date falls within the presented time period 1 year prior.
• i.e. in a visual showing July 2024, I want to evaluate the 1 year retention status of all employees hired in July 2023
• Of the identified 'Hired' population from above, determine how many of those employees exist in the Termination FACT table with a Tenure of <1 year

My Issue is that I've been unable to find a DAX measure that correctly identifies the terminated employees and maintains the row level date filter context.  I either return a value on the grand total row only or nothing is returned.

Current Matrix Visual

Expected outcomes for July and August 2024

DAX Measures for identifying relevent terminations are:

TerminatedWithin1YearV1 = 
VAR CurrentYear = YEAR(MAX('Date'[Date]))
VAR CurrentMonth = MONTH(MAX('Date'[Date]))
VAR FirstDayOfMonthPriorYear = DATE(CurrentYear - 1, CurrentMonth, 1)
VAR LastDayOfMonthPriorYear = EOMONTH(FirstDayOfMonthPriorYear, 0)

RETURN
    CALCULATE(
        DISTINCTCOUNT(Terms[Empl_ID]),
        FILTER(
            Terms,
            Terms[Consecutive Tenure (Yrs)] < 1 &&
            Terms[Empl_ID] IN
            SELECTCOLUMNS(
                FILTER(
                    Hires,
                    Hires[MostRecentHireDate] >= FirstDayOfMonthPriorYear &&
                    Hires[MostRecentHireDate] <= LastDayOfMonthPriorYear
                ),
                "Empl_ID", Hires[Empl_ID]
            )
        )
    )

AND

TerminatedWithin1YearV2 = 
VAR SelectedDate = SELECTEDVALUE('Date'[Date]) -- Row-specific context
VAR CurrentYear = YEAR(SelectedDate)
VAR CurrentMonth = MONTH(SelectedDate)
VAR FirstDayOfMonthPriorYear = DATE(CurrentYear - 1, CurrentMonth, 1)
VAR LastDayOfMonthPriorYear = EOMONTH(FirstDayOfMonthPriorYear, 0)

VAR RelevantHires = 
    FILTER(
        Hires,
        Hires[MostRecentHireDate] >= FirstDayOfMonthPriorYear &&
        Hires[MostRecentHireDate] <= LastDayOfMonthPriorYear
    )

VAR RelevantTerms =
    FILTER(
        Terms,
        Terms[Consecutive Tenure (Yrs)] < 1 &&
        Terms[Empl_ID] IN SELECTCOLUMNS(RelevantHires, "Empl_ID", Hires[Empl_ID])
    )

RETURN
    CALCULATE(
        DISTINCTCOUNT(Terms[Empl_ID]),
        RelevantTerms,
        REMOVEFILTERS('Date') -- Prevent interference from other filters
    )

The date logic used in the v1 measure is the same that I used for the measure that counts the number of hires in the same month of the prior year.  That measure is calculating correctly, however when I attempt that same approach with the Terms measure, I get no row level returns, only a grand total for one month.
Appreciate any insight the community can provide.  Thanks in advance.

Jason

[lbendlin]

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]

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]

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]

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.