Help with Employee data

[Geeco1]

I have 3 tables that contain employee data.  The first contains a list of all employees (current and termed).  The 2nd table contains a list of the employees Hire information, including HireDate.  An employee could have multiple records in this table as they may have been hired multiple times (summer help for example).  The 3rd table contains termination data including TermDate.  Again, there can be multiple records for each employee in this table.  All 3 tables are joined by EmployeeID.

I need to create a measure that will produce a count of active employees on a given date (HireDate < given date and TermDate > given date).  

Where I am running into issues is trying to create the measure and filter the data from different tables.

Any suggestions are appreciated.

[Cmcmahan · Accepted solution]

How are you determining what the "given date" is?  Is it selected through a filter?  Against a date dimension table or against another table?  Does EVERY employee in your data have a Start Date, potentially a Term Date, and potentially multiple of both?

I'm going to say [Selected Date] is the given date, but you will need to replace that with something more specific, depending on if you're using a date dimension or other method.

Active Employees = CALCULATE( DISTINCTCOUNT(HireTable[EmployeeID]), FILTER(ALL(HireTable), HireTable[HireDate] <= [Selected Date]) ) - 
                   CALCULATE( DISTINCTCOUNT(TermTable[EmployeeID]), FILTER(ALL(TermTable), TermTable[TermDate] <= [Selected Date]) )

This is just calculating the number of distinct hires you have before a given date, and subtracting terminations from before that date. If this doesnt' work for your purposes, or you have a better calculation you want done, let us know.

[Geeco1]

Thanks.... That seemed to work. 

[Geeco1]

I'll try your suggestion and see if that works for me.  The "given date" is derived from a date slicer.  Every employee "should" have a HireDate, but not everyone will have a TermDate (still employeed).

[Cmcmahan]

I was mostly trying to figure out what field that date slicer is using to slice.  Is it slicing on HireDate? Is it slicing on a date dimension? Some other date field?

[Geeco1]

I have a calendar dimension table that the slicer is applied to.  The Calendar table is linked to both the Hire table and the Term table via the date field.

[Cmcmahan]

Then you should be able to replace [Selected Date] with SELECTEDVALUE(calendar[Date]) in the above and have it work.