Headcount by date, derived from Hire, Rehire, and Termination date columns

[danPiech10]

I have an Employee Dimension table named 'Census' and a generated Calendar table. These are joined on 'Census'[HIRE_DATE] = 'Calendar'[Date].  I am trying to create a line chart that shows the number of active employees over time.

In 'Census' I have [HIRE_DATE], [TERM_DATE], and [REHIRE_DATE]. If an employee has been rehired, the [TERM_DATE] column is null, so I don't know the date of their first departure but I would know the date of there most recent departure if they'd been rehired and then quit. Because I don't know the date of their first termination I have settled on consolidating the HIRE and REHIRE date columns into [IF_HIRE_DATE]; if REHIRE is null then HIRE else REHIRE.

I have built an accurate daily headcount line chart using a calculated column in the 'Calendar' table with the following DAX:

HEADCOUNT =
SUMX(Census,
IF(
(ISBLANK(Census[TERM_DATE]) && 'Calendar'[Date] >= Census[IF_HIRE_DATE])
||
('Calendar'[Date] >= Census[IF_HIRE_DATE] && 'Calendar'[Date] <= Census[TERM_DATE])
,1,0)
)

Unfortunately, I have found that this calculated column really slows down the report. But more importantly, the numbers in the line chart do not respond to any filters applied to the 'Census' table. I recognize that even if I were able to get the filters to apply to this column, that it would be extremely slow. Ideally, I'd be able to build a similar functionality in a measure, but I'm at a loss.

[danPiech10]

I see what the conditions in the AND / OR statements are doing, but as a whole, the formula doesn't seem to work as a column or measure. "Multiple Columns cannot be converted to a scalar value."

[danPiech10 · Accepted solution]

It's very similar to the calculated column I have in the OP, but I managed to rework it to be used in a measure.

m.Headcount = 
CALCULATE(
    COUNT(Census[EMPLOYEE_ID]),
        ALLSELECTED(Census),
        (ISBLANK(Census[TERM_DATE]) && Census[IF_HIRE_DATE] <= MAX('Calendar'[Date]))
        ||
        (Census[TERM_DATE] >= MAX('Calendar'[Date]) && Census[IF_HIRE_DATE] <= MAX('Calendar'[Date]))
    )

[amitchandak]

@danPiech10 , Try like

Current Employees = CALCULATE(COUNTx(FILTER(Employee,
(Employee[Hire Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date])))
||
(
(not(isblank(Employee[Rehire Date])) || Employee[Rehire Date]<=max('Date'[Date])) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))))
,CROSSFILTER(Employee[Start Date],'Date'[Date],None))
)

refer

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...