Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

6 REPLIES 6
Anonymous
Not applicable

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.

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Daniel,

 

How can i send u the data file?

v-danhe-msft
Employee
Employee

Hi @Anonymous,

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

1.PNG

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:

3.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.