Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Thanks a lot in advance
Solved! Go to Solution.
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
Hi @Anonymous
do you have a date table or only one table similar to the sample data you've provided in your post?
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
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,
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
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.
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
@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)
@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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |