Getting latest instance in a row of events

[Honne2021]

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

[amitchandak · Accepted solution]

@Honne2021 , refer if my blog on the same topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0