Retention Rate

Hi Anonymous 

 

Here's an option for a Retention Rate measure.

It gets a list of employees at the start of the period, another list of those at the end, then uses INTERSECT to see employees in both.  I've assumed a blank end date means the employee hasn't left.

The IF function is to stop you getting a retention rate until the period is finished.

Retention Rate = 
VAR _StartOfPeriod = MIN('Date'[Date])
VAR _EndOfPeriod = MAX('Date'[Date])
VAR _Result = 
IF( TODAY() >= _EndOfPeriod,
    VAR _EmployeesAtStart = 
    CALCULATETABLE(
        VALUES(Employees[Employee_ID]),
        Employees[StartDate] <= _StartOfPeriod,
        Employees[EndDate] >= _StartOfPeriod || ISBLANK(Employees[EndDate])
    )
    VAR _EmployeesAtEnd = 
    CALCULATETABLE(
        VALUES(Employees[Employee_ID]),
        Employees[StartDate] <= _EndOfPeriod,
        Employees[EndDate] >= _EndOfPeriod || ISBLANK(Employees[EndDate])
    )
    VAR _NoOFEmployeesAtStartAndEnd = 
    COUNTROWS(
        INTERSECT(_EmployeesAtStart, _EmployeesAtEnd)
    )
    RETURN
        DIVIDE(_NoOFEmployeesAtStartAndEnd, COUNTROWS(_EmployeesAtStart))
)
RETURN
    _Result