topic Retention Rate in DAX Commands and Tips

Retention Rate

Anonymous — Wed, 09 Feb 2022 20:55:25 GMT

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

Re: Retention Rate

PaulOlding — Wed, 09 Feb 2022 22:40:19 GMT

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

Re: Retention Rate

Whitewater100 — Thu, 10 Feb 2022 15:54:21 GMT

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

Re: Retention Rate

tamerj1 — Thu, 10 Feb 2022 17:34:41 GMT

Hi Anonymous

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

Re: Retention Rate

Anonymous — Thu, 10 Feb 2022 17:40:10 GMT

Hi tamerj1
I do have a date table.

Re: Retention Rate

Anonymous — Thu, 10 Feb 2022 18:02:28 GMT

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.

Re: Retention Rate

PaulOlding — Thu, 10 Feb 2022 18:14:22 GMT

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

Re: Retention Rate

Anonymous — Thu, 10 Feb 2022 18:25:47 GMT

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

Re: Retention Rate

PaulOlding — Thu, 10 Feb 2022 18:29:34 GMT

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)

Re: Retention Rate

Anonymous — Thu, 10 Feb 2022 18:31:47 GMT

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.

Re: Retention Rate

Anonymous — Thu, 10 Feb 2022 18:36:28 GMT

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?

Re: Retention Rate

Whitewater100 — Thu, 10 Feb 2022 19:02:38 GMT

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,

Re: Retention Rate

PaulOlding — Thu, 10 Feb 2022 19:25:58 GMT

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.