Churn rate by tenure group and age group

ejafarov · ‎04-04-2023

Dear Power BI Community,

I hope this message finds you well. I am currently working on a project where I need to calculate the churn rate for combinations of tenure and age groups in Power BI. I have multiple measures that find employee count for active employees and employees 12 months before the selected date, for each combination of tenure groups and age groups.

My goal is to find the churn rate (turnover) for each combination of tenure and age groups for active employees and employees 12 months before the selected date.

Here are the measures I have:

ActiveEmployeeCount:
EmployeeCount12MonthsBefore
Employee count left during 12 months

I need help creating a measure to calculate the churn rate and then display the results in a matrix visualization.

I have tried the following DAX formula to calculate the churn rate, but I would appreciate it if someone could confirm whether it's correct or suggest an alternative method:

v-tangjie-msft · ‎04-10-2023

Hi @ejafarov ,

Please try this:

Measure one Filtered =

CALCULATE (

[Cnt_Active_AgeGr],

FILTER (All('Join of age and tenure'), MAX('Join of age and tenure'[Tenure Bucket for active] )= MAX('Join of age and tenure'[Tenure Group]) &&

MAX('Join of age and tenure[Age Bucket for active]) =MAX('Join of age and tenure'[Age Group])

)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

ejafarov · ‎04-11-2023

Dear,

Sorry for the unclear format. I am adding the sample table from my file.

Currently, I have measures to calculate the count of employees for age group and tenure group separately. However, I need a DAX measure that will consider the selected filter (e.g., 31.12.2022) and calculate the active employee count by a combination of age group and tenure group. Ultimately, I want to find the churn rate based on this data.

Could you please explain me steps or dax?

Thanks

ID	Start Date	Resignation date	Birth Date
19	25/02/2009		07/02/1985
37	11/05/2012		24/12/1980
980	01/03/2017		24/12/1992
1677	15/04/2022		18/06/1981
990	20/12/2021		09/07/1987
1259	03/06/2019		09/02/1986
912	31/10/2016		19/01/1988
1216	11/02/2019	28/12/2022	25/09/1994
1171	01/10/2018		11/04/1995
1540	10/06/2021		22/11/1979
1256	01/05/2019	14/01/2022	07/10/1986
1245	17/01/2022	12/05/2022	07/10/1986
1664	01/03/2022		26/01/1993
1395	06/03/2020		15/06/1977
1057	01/12/2017		09/05/1996
1362	10/01/2020		12/01/1990
1082	05/02/2018		06/06/1995
1252	20/05/2019	05/05/2022	23/08/1985
1422	01/07/2020		27/05/1988
1775	16/12/2022		05/01/1999
1488	05/01/2021		29/01/1994
1401	13/03/2020		02/06/1966
85	01/02/2013		08/12/1990
671	14/09/2016		29/03/1982
1092	02/04/2018		26/05/1995
1560	01/07/2021		27/09/1999
1779	19/12/2022		20/03/2001
290	24/08/2015		06/11/1991
1634	09/12/2021	09/03/2022	11/01/1997
1695	25/05/2022	09/09/2022	14/04/2001
1479	01/12/2020		30/01/1991
1534	01/06/2021	06/06/2022	08/12/1982
1358	08/01/2020		23/04/1993

v-tangjie-msft · ‎04-12-2023

Hi @ejafarov ,

Do you mean you want the measure results to be affected by the slicer?

You can use ALLSELECTED () function.

Please refer to the following documents for more information.

Introducing ALLSELECTED in DAX - SQLBI

Calculating Employee Attrition Rate with DAX – Part 1 (mssqltips.com)

Customer Churn Rate - Microsoft Power BI Community

Churn Rate Measure - Microsoft Power BI Community

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

ejafarov · ‎04-04-2023

Measure one Filtered =

CALCULATE (

[Cnt_Active_AgeGr],