Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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