topic Re: Employee Turnover % by Month in DAX Commands and Tips

Employee Turnover % by Month

Anonymous — Thu, 20 Aug 2020 20:50:49 GMT

Hey guys, I've been struggling to find specific DAX answers to this above question, although it appears its been asked multiple times.

I'm attemping to provide the Employee Turnover % by Month - the formula for this is: (Employees Terminated / (Count Employees on first day of the month + Count of Employees on last day of the month) / 2)

My data fields are as follows: NAME, HIRE DATE, TERMINATION DATE, STATUS(Active or Inactive)

I have a date table set up currently with 2 separate inactive relationships with the hire date and termination date, which was required for doing a rolling 12 month calculation that I found through this forum below.

I think I need help with DAX formulas for:

1. Count of Employees at Start of Month
2. Count of Employees at End of Month
3. Count of Employees Terminated during the Month

I found a solution through this forum for a Rolling 12 Month Average for Turnover % here(2 links below), and it was very helpful, but I need the by month %:
https://finance-bi.com/power-bi-employee-count-by-month/
https://finance-bi.com/power-bi-employee-turnover-rate/#:~:text=The%20Employee%20Turnover%20Rate%20compares,a%20turnover%20rate%20of%2020%25.

Re: Employee Turnover % by Month

amitchandak — Fri, 21 Aug 2020 05:29:29 GMT

Anonymous , Please check if my blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Employee Turnover % by Month

Anonymous — Fri, 21 Aug 2020 14:56:12 GMT

// Truth is you don't have to limit the calculation // to just a month. You can calculate what you want // on ANY period of time. This will, of course, // also work for months in particular. // Please do NOT connect your Dates table to any // of the fields in the Employees table. [# Emps At Start Of Period] = // These are emps that have // hire date <= start of period var __periodStart = MIN( Dates[Date] ) return CALCULATE( countrows( Employees ), KEEPFILTERS( Employees[Hire Date] <= __periodStart ) ) [# Emps At End Of Period] = // These are emps that have // their hire date <= end of period // and their termination date >= // end of period. var __periodEnd = MAX( Dates[Date] ) return CALCULATE( countrows( Employees ), KEEPFILTERS( Employees[Hire date] <= __periodEnd ), KEEPFILTERS( Employees[Termination Date] >= __periodEnd || isblank( Employees[Termination Date] ) ) ) [# Emps Terminated During Period] = // self-explanatory var __periodStart = MIN( Dates[Date] ) var __periodEnd = MAX( Dates[Date] ) return CALCULATE( countrows( Employees ), KEEPFILTERS( __periodStart <= Employees[Termination Date] ), KEEPFILTERS( Employees[Termination Date] <= __periodEnd ) )

Re: Employee Turnover % by Month

Anonymous — Fri, 21 Aug 2020 14:28:04 GMT

Anonymous

Right away when I'm trying to implement the first DAX for "# Emps At start of period" I'm getting an error that "Too many arguments were passed to the COUNTROWS function, maximum is 1." Is there some other kind of manipulation I need to do to the hire date field in my data?

Re: Employee Turnover % by Month

Anonymous — Fri, 21 Aug 2020 14:58:03 GMT

This is because I've got too many things to think of and I don't create models to check my code. I just write it. Try the code above now.

Re: Employee Turnover % by Month

Anonymous — Fri, 21 Aug 2020 15:05:36 GMT

Thanks for the quick response, I'll try in the next few minutes and then give you an update.