Employee Turnover % by Month

[Anonymous]

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

[Anonymous · Accepted solution]

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

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

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]

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

[amitchandak]

@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