Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Unique count of active employees given certain month



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 =
CALCULATETABLE ('Employeehist';'Employeehist'[Startdate] <= MaxDate; ALL ( 'Date') )

( 'Employeehist'[Enddate] ) > MaxDate)



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])


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!!

Community Champion
Community Champion

Hi @JustaRookie012 ,


See if this helps.


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

Super User
Super User
Super User
Super User

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.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors