Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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%20compares,a%20turnover%20rate%20of%2020%25.

 

  • Anonymous's avatar
    Anonymous
    6 years ago

     

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

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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.