Employee Turnover

[Anonymous]

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 Data Sheet.

- Each of them have their Hire date and Last Employment Date (either blank or have a date).

Calender Sheet

- Date table with different cuts, like Year, Month, Period Number (e.g. 201801, 201802), Period Start Date (1st of each month), Period End Date (End date of each month)

And I need 3 calculation:

1. Monthly Beginning Headcount:
   AND(Hire Date <=1st of Each Month, OR(Last Employment Date = BLANK, Last Employment Date >= 1st of Each Month))
2. Monthly End Headcount:
   AND(Hire Date <=End of Each Month, OR(Last Employment Date = BLANK, Last Employment Date >End of Each Month))
3. Monthly Turnover Headcount:
   AND(Last Employment Date >=1st of Each Month, Last Employment Date <=End of Each Month)

After getting the above 3 numbers, i can get the Monthly Turnover Rate:

Monthly Turnover Headcount / ((Monthly Beginning Headcount + Monthly End Headcount)/2)

However, i dont know how to set the DAX formula of calculating No. 1 - 3.

[Anonymous]

Hi Daniel,

Thanks for your suggestion in advance.

May i ask a stupiad question thay is PowerBI calculation same as PowerPivot in Excel?

Beg HC 4:=CALCULATE(TeamMemberList[Employee ID],filter(TeamMemberList,[Last Hire Date]<=MIN('Calendar'[Period Start Date])&&([Last Employment Date]=BLANK()||[Last Employment Date]>=MIN('Calendar'[Period Start Date]))))

End HC 4:=CALCULATE(TeamMemberList[Employee ID],FILTER(TeamMemberList,[Last Hire Date]<=MAX([Last Employment Date])&&([Last Employment Date]=BLANK()||[Last Employment Date]>MAX('Calendar'[Period End Date]))))

Leaver 4:=CALCULATE(TeamMemberList[Employee ID],FILTER(TeamMemberList,TeamMemberList[Last Employment Date]>=MAX('Calendar'[Period Start Date])&&TeamMemberList[Last Employment Date]<=MAX('Calendar'[Period End Date])))

But all shown in errow. I have already linked the calendar table & TeamMemberList table in the diagram view.

[v-danhe-msft]

Hi @Anonymous,

The calculation in PowerBI is same as PowerPivot in Excel.

The measure could work on my side, could you please share your data file or pbix if possible?

Regards,

Daniel He

[Anonymous]

Hi Daniel,

How can i send u the data file?

[v-danhe-msft]

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]

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]

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.