Calculating Effective headcount

[amitksingh2003]

Hello Experts,

I am trying to create a Headount/FTE dashboard. I have employee records like below table. I have also added a calendar table for time slicer. Everything works great. Thanks to this wonderful forum. I came here whenever stuck and plenty of previous similar problems/solutions to refer to.

Emp ID	Emp Name	Department	Level	Joining Date	Exit Date	Status
YYYY0001	aaa	xyz	Level2	15-May-21	14-Nov-21	Exited
YYYY0002	bbb	xya	Level5	14-Feb-21	31-Mar-99	Active
YYYY0003	ccc	xyb	Level3	9-Jul-21	31-Mar-99	Active

I wanted to further extend this dashboard to be used as a tool for heacount/FTE forecast. I am trying to calculate effective headcount for this purpose. I mean when I am calculating effective headoucnt for the month of say Feb'21, employee ID YYYY0002 should be counted as 0.5 instead of 1 as the employee has joined in the middle of the month. 

The Summary HC table should look like below:

Month	Closing Headcount	Effective Headcount
Feb'21	1	0.50
Mar'21	1	1.00
Apr'21	1	1.00
May'21	2	1.52
Jun'21	2	2.00
Jul'21	3	2.71
Aug'21	3	3.00

I am using below measure to get headcount numbers successfully. However, stuck at effective headcount calculation. Should I use Measures or add custom columns!

CALCULATE(DISTINCTCOUNT('Employee_Table'[Emp ID]),FILTER(ALL('Calendar'[Date]),[Min Date]=[Min Date]),'Employee_Table'[Joining Date]<MIN('Calendar'[Date]))

I tried below measure to get employee level effective headcount number. This seems wrong because of the way I am trying to get employee level Exit/Joining date. It's actually giving Min/Max date from column, can be for any employee. How do I get employee level Exit/Joining Date!

(MIN(MIN('Employee_Table'[Exit Date]),MAX('Calendar'[Date]))-MAX(MIN('Employee_Table'[Joining Date]),MIN('Calendar'[Date])))/(MAX('Calendar'[Date])-MIN('Calendar'[Date])+1)

Thanks

[Greg_Deckler · Accepted solution]

@amitksingh2003 I have an FTE calculation in DAX Cookbook, Recipe 5 in Chapter 7: PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com) That's a link to the GitHub repository for the PBIX files used.

[amitksingh2003]

Awesome Greg, this is exactly what I was looking for 🙂