Count based on date compared to two fields in another table

[northshorehiker]

I have a table containing a list of "week ending" dates. I have another table of employees, including their hire and termination dates. I'd really like to add a calculated column to the table of dates that counts the number of active employees as of that date, so it would be a count of the rows in the employee table where the employee hire date is prior to the "week ending" date and the termination date is after the "week ending" date.

Does that make sense? Any assistance is most appreciated.

==

Chris

[mwegener · Accepted solution]

Something like this?

No of Employees =
CALCULATE (
    COUNT ( employee[employee] ),
    FILTER (
        employee,
        AND (
            employee[hire date] <= 'week ending'[week ending],
            employee[termination date] > 'week ending'[week ending]
        )
    )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[northshorehiker]

Thank you so much for answering my noob question. That's exactly what I was after.

==

Chris