cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Employee Turnover % by Month

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%20c...

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````// 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.
// 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 )
)``````

5 REPLIES 5
Anonymous
Not applicable

``````// 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.
// 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 )
)``````

Anonymous
Not applicable

@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?

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

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

Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors