Skip to main content
cancel
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

Reply
JustaRookie012
Frequent Visitor

Unique count of active employees given certain month

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.

 

JustaRookie012_1-1597701404517.png

 

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

3 REPLIES 3
harshnathani
Community Champion
Community Champion

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
Super User
Super User
lbendlin
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

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.