Unique count of active employees given certain month

[JustaRookie012]

Greetings,

I have the following dataset containing the history of employees. Each mutation of an employee is recorded, hence the possible multiple records per employee (as you can see circled). The first record given the same employee is the most recent mutation, whereas the second record is the oldest. So for example employee Nr.30 has a Startdate of 17-9-1990 which is also his hireDate and ContractEndDate of 31-12-2999. Then he got a function change starting at 9-9-2019 but his ContractEndDate remains the same 31-12-2999. So basically the MIN startdate is equal to his HireDate and the MAX EndDate is equal to his ContractEndDate.

Now I am interested in the amount of (unique) active employees given certain month end date. I have already tried the following:

# HC end of the month:=
VAR MaxDate = MAX (Date[Date] )

VAR Emplycount =
CALCULATE (
COUNTROWS(
CALCULATETABLE ('Employeehist';'Employeehist'[Startdate] <= MaxDate; ALL ( 'Date') )

);
( 'Employeehist'[Enddate] ) > MaxDate)

RETURN
Emplycount

But as you can see, this will count every row that meets the criteria and since the table contains multiple records of the same employee, this will give a wrong number. 

I have also tried the following incorporating the min and max logic mentioned above, but still without the desired result: 

# HC end of the month:=
VAR MaxDate = MAX('Date'[Date])

RETURN

SUMX('Employeehist';
VAR employeeStartDate = MIN([Startdate])
VAR employeeEndDate = MAX([Enddate])
RETURN IF(employeeStartDate<= MaxDate && employeeEndDate> MaxDate; 1; 0 ))

Besides the Headcount, I am also looking for the FTE percentage of the active employees. Hereby the FTE column is used, a number of 1 indicates a full time employee that works 100%. 0,5 indicates someone that works 50% etc.

I am really stuck, any help will be appreciated!!

[harshnathani]

Hi @JustaRookie012 ,

See if this helps.

https://www.youtube.com/watch?v=rsx43g7TBBs&t=1288s

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@JustaRookie012 , check if my blog on similar stuff can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[lbendlin]

Familiarize yourself with the CROSSFILTER(,,NONE) pattern to blend your data with a dates/calendar table in a cartesian product. That will then allow you to compute the status for each employee on any given day in your dates/calendar table.