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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

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?



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

Hi Daniel,


How can i send u the data file?


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)



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



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


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)
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]
Naeem Khan
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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.