Forum Discussion
undefined
Hi All, I need to create a power BI with attrition rate calculation that can drill down by Department, country and manager. I have two files, headcount and Termination. Need help in DAX
Thankyou for quick response.
Sorry for confusion, the DAX is not helping.
Below is the corrected dataset:
I need help in creating 2 DAX.
1. Monthly Attrition Rate -> Total termination in previous month/ Average of (Previous HC + Current HC)
2. YTD / Cumulative Attrition Rate -> Total terminations from April 01 / Average of (HC in April 01 + Current HC)
Example:
Monthly Attrition Rate for Jun -> Terminations in Jun / Average (HC in May + HC in Jun)
YTD / Cumulative Attrition Rate for Jun -> Terminations From April - jun / Average ( HC in April + HC in Jun)
YTD / Cumulative Attrition Rate for Jul = Terminations From April - jul / Average ( HC in April + HC in Jul)
Below is the sample data:
| Reporting Date | Employee ID | Employee Name | Department | Country | Status | Terminated Date |
| 4/30/2023 | Employee 1 | ABC | Finance | Finland | Active | |
| 4/30/2023 | Employee 2 | ABC | HR | US | Active | |
| 4/30/2023 | Employee 3 | ABC | IT | Canada | Active | |
| 4/30/2023 | Employee 4 | ABC | SCM | India | Active | |
| 4/30/2023 | Employee 5 | ABC | Finance | Finland | Active | |
| 4/30/2023 | Employee 6 | ABC | IT | US | Terminated | 4/2/2023 |
| 4/30/2023 | Employee 7 | ABC | HR | India | Terminated | 4/10/2023 |
| 5/31/2023 | Employee 1 | ABC | Finance | Finland | Active | |
| 5/31/2023 | Employee 2 | ABC | HR | US | Active | |
| 5/31/2023 | Employee 3 | ABC | IT | Canada | Terminated | 5/15/2023 |
| 5/31/2023 | Employee 4 | ABC | SCM | India | Active | |
| 5/31/2023 | Employee 5 | ABC | Finance | Finland | Active | |
| 5/31/2023 | Employee 8 | ABC | IT | US | Active | |
| 5/31/2023 | Employee 9 | ABC | HR | India | Active | |
| 5/31/2023 | Employee 10 | ABC | HR | India | Active | |
| 5/31/2023 | Employee 11 | ABC | HR | India | Active | |
| 5/31/2023 | Employee 12 | ABC | HR | India | Active | |
| 5/31/2023 | Employee 13 | ABC | HR | India | Active | |
| 5/31/2023 | Employee 14 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 1 | ABC | Finance | Finland | Active | |
| 6/30/2023 | Employee 2 | ABC | HR | US | Active | |
| 6/30/2023 | Employee 4 | ABC | SCM | India | Active | |
| 6/30/2023 | Employee 5 | ABC | Finance | Finland | Active | |
| 6/30/2023 | Employee 8 | ABC | IT | US | Active | |
| 6/30/2023 | Employee 9 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 10 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 11 | ABC | HR | India | Terminated | 6/27/2023 |
| 6/30/2023 | Employee 12 | ABC | HR | India | Terminated | 6/27/2023 |
| 6/30/2023 | Employee 13 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 14 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 15 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 16 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 17 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 18 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 19 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 20 | ABC | HR | India | Active | |
| 6/30/2023 | Employee 21 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 1 | ABC | Finance | Finland | Terminated | 7/1/2023 |
| 7/31/2023 | Employee 2 | ABC | HR | US | Active | |
| 7/31/2023 | Employee 4 | ABC | SCM | India | Active | |
| 7/31/2023 | Employee 5 | ABC | Finance | Finland | Active | |
| 7/31/2023 | Employee 8 | ABC | IT | US | Active | |
| 7/31/2023 | Employee 9 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 10 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 13 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 14 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 15 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 16 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 17 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 18 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 19 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 20 | ABC | HR | India | Active | |
| 7/31/2023 | Employee 21 | ABC | HR | India | Active |
