Turnover- employees gained and lost each month

[joshcomputer1]

I am needing to calculate a montly turnover rate.  Let's start with the count gained and lost for each team. 

I have the following columns

Table = MasterAll

Columns

Start date

End date

*End Date is 12/31/2026 if active

Team

Datetable1[yearmonthnum]

Here is what I want the table to look like....

Team                Jan    Feb    March

Team 1-gain       1       2        5

Team 1- loss       3      1         4

So I think I need two measures, one for employees lost and one for employees gained per month. I have started on a possible measure, but on my formula bar, the datetable[yearmonthnum] is underlined so this doesn't work. 

Headcount Gained = calculate(count(MasterAll[Employee]), filter(All(masterall), masterall[start date]<= datetable[yearmonthnum] && masterall[end date]>=datetable[yearmonthnum]))

[v-yulgu-msft]

Hi @joshcomputer1,

Could you please try below measure, I made a little modification to your original one:

Headcount Gained =
CALCULATE (
    COUNT ( MasterAll[Employee] ),
    FILTER (
        ALL ( masterall ),
        masterall[start date] <= MAX ( datetable[yearmonthnum] )
            && masterall[end date] >= MAX ( datetable[yearmonthnum] )
    )
)

For more advice, please provide some sample data (detailed records in MasterAll table) so that I can test for you. Also, please describe how to determine whether a team or employee is gained or lost.

Regards,

Yuliana Gu

[joshcomputer1]

Employee gained is if their start date is during the month. Employee lost if their end date is during the month.

Here is a link to the dropbox file with a small sample of rows. 

https://www.dropbox.com/s/dzpg08ap10x7hp8/Book1.xlsx?dl=0

I think that the measure you wrote gets a count of employees that were working during the month (I will need this later I am sure), but what I want for employees gained is to know how many employees started each month.  We don't need to look at the end date at all for employees gained.  

The 'employees lost' is a count of employees whose end date is within each month.  

[joshcomputer1]

Headcount Lost = calculate(count(MasterAll[employee]), filter(All(masterall), countrows(filter(masterall, MONTH(earlier(MasterAll[End Date])) = month (max(DateTable1[date]))))

This seems to work except that December shows 136. It should be 9

[joshcomputer1 · Accepted solution]

This was easier than anticipated. I ended up using employee count as employees hired. The start date is active relationship to the datetable.  We have non billable and bench people that need to be excluded though so I added that there. 

Employees Hired = calculate(countrows(masterall), masterall[title]<>"non-billable" && masterall[title]<>"bench")

For employees lost each month, I used an inactive relationship between the end date and my date table (datetable1). It's really just a count but pointing to the inactive relationship. 

Headcount Lost2 = calculate(count(MasterAll[employee]),  userelationship(datetable1[date], masterall[end date]))