Forum Discussion
Help with Employee data
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.
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.
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).
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?
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.
Thanks.... That seemed to work.