Need PowerPivot (DAX) measure to determine number of active employees at any point in time

[Sbakker1]

I'm able to create the calculation in Excel that returns the number of active employees for any given date. I'm hopeful that there is a way to do this dynamically in PowerPivot so I can return the accurate count regardless of filters.
The employee table contains the fields 'Hire Date', 'Employee Status', and 'Activity Date' amongst others. So to get the number of active employees for any date the formula would be :
Generic: Active Employees = COUNTIFS([Hire Date], <= Selected Date , [Employee Status] = "Active") + COUNTIFS([Hire Date], <= Selected Date , [Employee Status] = "Terminated", [Activity Date] >=Selected Date )

Actual formula:
=COUNTIFS(EmpData[[#All],[Hire Date]], "<="&R1, EmpData[[#All],[Employee Status]], "Active") + COUNTIFS(EmpData[[#All],[Hire Date]], "<="&R1, EmpData[[#All],[Employee Status Date]], ">="&R1, EmpData[[#All],[Employee Status]], "Terminated")

I've also used Sumproduct to get the same result.
Actual formula:
=SUMPRODUCT((EmpData[[#All],[Hire Date]]<=R1)*(EmpData[[#All],[Employee Status]]="Active")) + SUMPRODUCT((EmpData[[#All],[Hire Date]]<=R1)*(EmpData[[#All],[Employee Status]]="Terminiated")*(EmpData[[#All],[Employee Status Date]]>=R1))

To break that down, I want a count of the active employees that were hired on or before the selected date, plus the terminated employees that were hired before the comparison date, but not terminated until after the selected date. For the PowerPivot model there are two tables, 'Employee data' and 'Fiscal Calendar'. The selected date will be from the 'Fiscal Calendar'.
Any thoughts on how to create a measure that will yield the correct results in PowerPivot?

Let me know if you need more information or an example.
Thank you.

 

[jorismoerings]

Hi,

Could you show us some sample data (preferably using XL2BB ) accompanied by the correct answer (based on the sample data provided)