Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Solved! Go to 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]))
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
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.
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
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |