cancel
Showing results for
Did you mean:

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

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:

AND(Hire Date <=1st of Each Month, OR(Last Employment Date = BLANK, Last Employment Date >= 1st of Each Month))
AND(Hire Date <=End of Each Month, OR(Last Employment Date = BLANK, Last Employment Date >End of Each Month))
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:

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

6 REPLIES 6
Anonymous
Not applicable

Hi Daniel,

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.

Employee

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?

Employee

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:

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

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

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

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.

Announcements

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

Top Solution Authors
Top Kudoed Authors