Calculate Retention & Turnover %

Hi Anonymous 

Due to I don't know your data model, I build a sample to have a test.

My Sample:

Build a Date table by dax code.

Date =
VAR _T =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "MMMM" )
    )
VAR _T2 =
    ADDCOLUMNS ( _T, "Rank", RANKX ( _T, [YearMonth],, ASC, DENSE ) )
RETURN
    _T2

Measures:

Retention - % calculated with = 
VAR _Previous12START =
    EOMONTH ( MAX ( 'Date'[Date] ), -13 ) + 1
VAR _Previous12END =
    EOMONTH ( MAX ( 'Date'[Date] ), -1 )
VAR _LEFT =
    CALCULATE (
        COUNT ( 'Sample'[Employee] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Check in Date] >= _Previous12START
                && 'Sample'[Check in Date] <= _Previous12END
                && 'Sample'[Date of Resignment] >= _Previous12START
                && 'Sample'[Date of Resignment] <= _Previous12END
        )
    ) + 0
VAR _Total =
    CALCULATE (
        COUNT ( 'Sample'[Employee] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Check in Date] <= _Previous12END
                && OR (
                    'Sample'[Date of Resignment] >= _Previous12START,
                    'Sample'[Date of Resignment] = BLANK ()
                )
        )
    )
RETURN
    1 - DIVIDE ( _LEFT, _Total )

Turnover - % calculated with = 
//Number of employees that left in the previous 12 months / (Total Employees in the previous 12 months + Number of employees that left in the previous 12 months)
VAR _Previous12START =
    EOMONTH ( MAX ( 'Date'[Date] ), -13 ) + 1
VAR _Previous12END =
    EOMONTH ( MAX ( 'Date'[Date] ), -1 )
VAR _LEFT =
    CALCULATE (
        COUNT ( 'Sample'[Employee] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Date of Resignment] >= _Previous12START
                && 'Sample'[Date of Resignment] <= _Previous12END
        )
    )
VAR _TOTAL =
    CALCULATE (
        COUNT ( 'Sample'[Employee] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Check in Date] <= _Previous12END
                && OR (
                    'Sample'[Date of Resignment] >= _Previous12START,
                    'Sample'[Date of Resignment] = BLANK ()
                )
        )
    )
RETURN
    DIVIDE ( _LEFT, _TOTAL )

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business. And show me more details about your calculate logic. You can show me the result you want by screenshot as well.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.