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.
Thanks.... That seemed to work.