Hi, I have a list of data that shows an employees job history each row. An employee may have 1 up to 30 or even 50 rows of data that signifies an event in their work. It can be a promotion, termination, re hire. The data looks like this (see below). My current formula only gets the employees TEAM at first instance when it should capture the latest TEAM. In this example, the employee as of date should be assigned in Supply Chain.
Formula: Current Employee Count = CALCULATE(DISTINCTCOUNT('Book3[Employee ID]), FILTER( 'Book3', GSC[Effective End Date] >= [Max Date]) -> to get the headcount of the current month
Max Date = MAX( Date) -> to get the max of the current month
| Employee ID | Hiring Date | Previous Team | Current Team | Event | Event Start | Event End | Seq |
| 1234500 | 28/01/2011 | | Tech | Hire | 01/01/2021 | 28/02/2022 | 1 |
| 1234500 | 28/01/2011 | Tech | Marketing | Promote | 01/03/2022 | 30/04/2022 | 2 |
| 1234500 | 28/01/2011 | Marketing | Supply Chain | Transfer | 01/05/2022 | 31/12/9999 | 3 |
