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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating active employees on a given date

Hello there,

 

Trying to count the number of employees at a given date in the past with the condition that their termination date needs to be either in future or value should be blank.

 

Active Workforce as of date =
Var
SelectedDate = SELECTEDVALUE('Calendar-Financial-10'[Date])
Return
CALCULATE(COUNTA('Headcount Report'[First name Last name]),
IF('Headcount Report - PowerBI - MB','Headcount Report - PowerBI - MB'[Termination Date]>=SelectedDate)
&& 'Headcount Report - PowerBI - MB'[Termination Date] <> BLANK())
1 REPLY 1
amitchandak
Super User
Super User

For Active employees on date or range, you need to make sure the join with the dates is not there.

When a range is selected in the slicer 

 

 

Active employee = 
Var   _start_date=(minx('Date','Date'[Date])) 
Var   _end_date=(maxx('Date','Date'[Date])) 

return
Active Employee calculate(count(employee_id),filter(employee, Hire_date<=_end_date && terminate_date >=_end_date,
CROSSFILTER(employee[Hire_date],date[date],None),
CROSSFILTER(employee[terminate_date],date[date],None)
)

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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