Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Retention Rate

Hi, 

I'm having trouble with employee retention rate. 

I need to calculate the [number of employees that where on the company at the beginning of a period and still are at the end] over [the number of employees at the beginning of that period]. In this calculation I need to ignore the employees that entered during this period. 

For example 

Date                 |     Employees

31/dec/2020    |      320

31/dec/2021    |      420

If I've hired 140 employees in 2021 I should have 460 employees, but I've lost 40. Some of those 40 can be from the 140 hired ones, so they cannot be considered. The problem is not that straightforward.

 

Let's use some data for example. What I have:

 

Employee_IDStartDateEndDate
106/04/2019       04/08/2021
206/04/2019       02/02/2022
306/04/2019       07/04/2019
406/04/2019 
507/10/2020       04/08/2021
607/10/2020       02/02/2022
707/10/2020 
807/10/2020 
905/09/2021       04/08/2021
1005/09/2021       02/02/2022
1105/09/2021 
1205/09/2021 

 

What I need: RETENTION RATE

 

 2019202020212022
Start of the year0378
Hired444 
Fired103 
End of the year378 
From Start to end035 
Retention Rate-%100%71% 

 

How can I make a DAX formula for this one? I'm a little lost here.

 

Thanks a lot in advance

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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

 

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

Hi @Anonymous 

do you have a date table or only one table similar to the sample data you've provided in your post?

Anonymous
Not applicable

Hi @tamerj1 
I do have a date table.

Whitewater100
Solution Sage
Solution Sage

Dear Raoni:

 

Please see attached file for most of your requirments. Right now it is creating a retension measure considering cumulative results. I hope this gets you going in the right direction. Please see link.

https://drive.google.com/file/d/1tFmI3MijMKb_Odvzr9ZKzXWgrlBA6DGl/view?usp=sharing 

Anonymous
Not applicable

Hi @Whitewater100 ... I'm looking your pbi and I have some questions. What does it mean "RT Working CT Inbound"? And in you case "Employee turnover"? because I know this one but in %, not in whole number.

Hi:

The RT measure shows on a cumulative basis how many employees have been hired. It starts off with 4 in June 2019. But in July one person was fired. So August will show 3 employees working and a turnover of 1. Becasue 3 of 4 are working so far you get a 75% retention rate. 

 

The measure continues to work like this and by the end you have 12 people in the RT calculation (total hires) and eight people working for a 66.6% retention rate. 8/12.

 

Hoefully this is moreclear now.

 

Thanks,

PaulOlding
Solution Sage
Solution Sage

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

 

Anonymous
Not applicable

Hi @PaulOlding ,

Thanks a lot for the code. 

I've done it and I'm not having the expected result. I've tried to figure it out but with no success.

 

With the code i'm having only one value in a chart X time, for the first period. All the others are empty. Any idea what could be the problem? I'll keep trying something here and if I get anything I update you here.

 

raonisilva_1-1644516083575.png

 

 

Hi  Is Ano a column that's in your date table?  The same one that's in this line of code?

VAR _StartOfPeriod = MIN('Date'[Date])

 

Also, is the date table disconnected from the rest of the model?

 

@Anonymous

Anonymous
Not applicable

@PaulOlding "Ano" is "Year" in portuguese. It's from my date table and it is all connected.

@Anonymous 

OK.  let's try disconnecting it from the rest of the model (or importing a new copy of the date table and using that)

Anonymous
Not applicable

@PaulOlding Wooow... It worked. I think... I have to make a hand calculation to check, but the values make some sense. I didnt knew I could use tables with no relation. Why does that work and not with?

@Anonymous 

For this measure we're handling filtering the fact table based on dates in the CALCULATETABLEs.  Let's say the date table was connected to start date and was filtered to March 2021.  The fact table would be filtered to include only rows where start date is in March 2021.  So your list of employees at the end of the period would not include those that started before March 2021.

Another option would be to use CROSSFILTER(Date[Date], Fact[Start Date], None) as a calculate modifier.  That would disable the relationship while performing the calculation.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors