Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I am quite new in Power BI and try to calculate 12 months rolling turnover.
The prefered calculation is as below.
sum(number of employee who left from Aug 2020 to July 2021)
average(number of active employee from Aug 2020 to July 2021)/12
My data structure was a simple HRIS file with employee ID, join date, terminate date, and department information.
I have employee around 9,000 people and would need to drill down turnover rate between Division and Department.
There was around 150 departments in the organization.
I tried google it but did not seem to find a way to solved this. I also tried create a calculated table and it took forever to complete due to the size of data. Below is the method that I have tried but it gave incorrect results.
https://finance-bi.com/blog/power-bi-employee-turnover-rate/
In the past, I use manual work around by calculate number of active and number of leaver in another file and then load and unpivot to use time intelligence function.
I'm quite lost now, can anyone help?
Thanks
Solved! Go to Solution.
@TanawatP , With help from a date table
Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )
Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
Avg last 12 months =
calculate(AverageX(Values('Date'[Month Year]) , [Current Employees]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
refer if needed
@TanawatP , With help from a date table
Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )
Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
Avg last 12 months =
calculate(AverageX(Values('Date'[Month Year]) , [Current Employees]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
refer if needed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |