Forum Discussion
Employee Turnover
Hi all, I am new to powerbi / data modelling and need to prepare a headcount & turnover rate report. My data like below: Team Member List - Each employee only have 1 row in the Employee Dat...
Thansk for sharing the formula. However I have to change them slightly (change || to && and brackets etc.) to work for our case. Our sample data is same as yours for calendar and data table.
Below are the details.
Sample Data
https://cdn1.imggmi.com/uploads/2019/5/15/325143b2bffc52e574ee3c097a8d2351-full.png
Code to generate Calendar Table
Month_Data =
var FullCalendar = ADDCOLUMNS(CALENDAR("1980/1/1","2021/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"MonthEnd",EOMONTH([Date],0),"MonthStart",EOMONTH([Date],-1)+1)
return
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[MonthEnd],[MonthStart])
Monthly Beginning Headcount = CALCULATE(COUNT(employees[employee_code]),FILTER('employees','employees'[hire_date]<MIN('Month_Data'[MonthStart])&&('employees'[last_working_date]=BLANK()||'employees'[last_working_date]>=MIN('Month_Data'[MonthStart]))))
Monthly End Headcount = CALCULATE(COUNT(employees[employee_code]),FILTER('employees','employees'[hire_date]<=MAX('Month_Data'[MonthEnd])&&(employees[last_working_date]=BLANK()||employees[last_working_date]>MAX(Month_Data[MonthEnd]))))
leavers = CALCULATE(count(employees[employee_code]),FILTER(employees,employees[last_working_date]>=MAX('Month_Data'[MonthStart]) && employees[last_working_date]<=MAX('Month_Data'[MonthEnd])))
joiners = CALCULATE(count(employees[employee_code]),FILTER(employees,employees[hire_date]>=MAX('Month_Data'[MonthStart]) && employees[hire_date]<=MAX('Month_Data'[MonthEnd])))
Avg HC = ([Monthly Beginning Headcount]+[Monthly End Headcount])/2
Attrition = ([joiners]-[leavers])/[Avg HC]
Turn Over = [leavers]/[Avg HC]
Regards
Naeem Khan
Hi Naeem,
Thank you so much for sharing. It worked so well.
I would like to calculate cumulated turnover rate too. Could you please guide me with that? I got wrong subtotal from Power BI.