Accurate Headcount using sumx and getting a distinct count

[bcobrien1977]

Hi,
I am trying to get an accurate headcount over time with a disconnected dates table. My problem is that  I need it to be a distinct count and I have employees who bill time to multiple cost centers.  Isolating for one employee I want to get a count of one and not 4 (the cost centers they billed time to).  Is there an easy way to do this? 

I do need them be able to see all 4 on a separate page so I can't deduplicate in power query.

Thanks

Employee Count UAT =

VAR selectedDate = MAX('Filter Dates'[Date])

RETURN

SUMX('vwHireAttrition_V2_UAT',

VAR employeeStartDate = 'vwHireAttrition_V2_UAT'[Hire Date]

VAR employeeEndDate = 'vwHireAttrition_V2_UAT'[Termination Date]

VAR TEST =IF(employeeStartDate<= selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

RETURN max(test,1)

[DataNinja777]

Hi @bcobrien1977 ,

In order to perform a flexible headcount analysis which respects the time dimension, you just need two tables.

1. An employee table with the information of

• Employee ID
• Start Date
• End Date

2. A calculated calendar table with min of hiring date and today() as the end date.  

Your are on the right track in approaching the task with a disconnected calendar table and employee table.  

My quesiton regarding your employee table is that you mentioned that employee hours are billed to different cost centers, but in that case, do you have the duration period of which cost centers are billed to in which period for a particualr employee?  Some employees transfer departments (cost centers) and headcount measure can handle such transfers respecting the time as reflected in the start and end date for a particular cost center duration for employees.  However, if the raw data is such that there are multiple cost centers for a given start and end date for employees, one way to simplify is to create a calculated employee table with only 3 required information mentioned above (namely, employee ID, start date, end date). 

Also, the correct employee measure logic is that employee start date is earlier than the selected date while at the same time (&&) end date is after the selected date for an employee to be counted as a headcount.  An example measure where there are changes in cost center start date and end date for employee is as shown below.  My observation regarding your measure is that it contained OR, but due to the reason I mentioned above, OR (||) should not feature in the headcount measure for it to work in the intended manner.  

Alternatively to a simplified calculated employee table which only shows unique headcount without duplicates relation to cost centers billed to, you can also use distinctcount of the employees by tweaking the measure above. 

There's an article discussing headcount measure in the link below. 

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

If you are stuck with your headcount measure, please let me know.

Best regards,

[rajendraongole1]

Hi @bcobrien1977 - can you try to use the below measure in your visualization. It will provide a distinct count of employees for the selected date, regardless of the number of cost centers 

Employee Count UAT =
VAR selectedDate = MAX('Filter Dates'[Date])

RETURN
CALCULATE(
DISTINCTCOUNT('vwHireAttrition_V2_UAT'[Employee ID]),
FILTER(
'vwHireAttrition_V2_UAT',
'vwHireAttrition_V2_UAT'[Hire Date] <= selectedDate &&
(
'vwHireAttrition_V2_UAT'[Termination Date] >= selectedDate ||
ISBLANK('vwHireAttrition_V2_UAT'[Termination Date])
)
)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!