Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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...
Need Help
Tutorial Requests
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
8 years ago

Hi Anonymous,

Based on my test, you could refer to below steps:

Create 4 measures:

Monthly Beginning Headcount = CALCULATE(COUNT(Table1[Member]),FILTER('Table1','Table1'[Hire data]<=MIN('Calender'[Start Date])||'Table1'[Last Employment Date]=BLANK()||'Table1'[Last Employment Date]>=MIN('Calender'[Start Date])))
Monthly End Headcount = CALCULATE(COUNT(Table1[Member]),FILTER('Table1','Table1'[Hire data]<=MAX('Table1'[Last Employment Date])||Table1[Last Employment Date]=BLANK()||Table1[Last Employment Date]>MAX(Calender[Period End Date])))
Monthly Turnover Headcount = CALCULATE(COUNT(Table1[Member]),FILTER('Table1','Table1'[Last Employment Date]>=MAX('Calender'[Start Date])&&Table1[Last Employment Date]<=MAX('Calender'[Period End Date])))
Monthly Turnover Rate = [Monthly Turnover Headcount]/(([Monthly Beginning Headcount]+[Monthly End Headcount])/2)

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/9av4z7i75quloo0/Employee%20Turnover.pbix?dl=0

 

Regards,

Daniel He

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.