Need formular to calculate turnover rolling 12 months

[TanawatP]

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 

[amitchandak · Accepted 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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[TanawatP]

Thanks @amitchandak  your solution work like a charm!