cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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_ID StartDate EndDate 1 06/04/2019 04/08/2021 2 06/04/2019 02/02/2022 3 06/04/2019 07/04/2019 4 06/04/2019 5 07/10/2020 04/08/2021 6 07/10/2020 02/02/2022 7 07/10/2020 8 07/10/2020 9 05/09/2021 04/08/2021 10 05/09/2021 02/02/2022 11 05/09/2021 12 05/09/2021

What I need: RETENTION RATE

 2019 2020 2021 2022 Start of the year 0 3 7 8 Hired 4 4 4 Fired 1 0 3 End of the year 3 7 8 From Start to end 0 3 5 Retention Rate -% 100% 71%

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

1 ACCEPTED SOLUTION
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``````

12 REPLIES 12
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.

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.

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.

Solution Sage

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,

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.

Solution Sage

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.

Solution Sage

@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?

Solution Sage

@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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors