Calculating active employees on a given date

[Anonymous]

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

[amitchandak]

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