Hi all, I have some data that is structured roughly like the below. Basically it tracks who reported to who, stacked by month. Obviously sometimes people change managers, so it's not exactly the same from month to month. In the below, Jane and Ross both report to Brad, and Katie reports to Jane. Jane leaves in June 2020 and Katie transfers to report to Ross instead.
| Month | Employee Name | Supervisory Name |
| April 2020 | Katie | Jane |
| April 2020 | Ross | Brad |
| April 2020 | Jane | Brad |
| May 2020 | Katie | Jane |
| May 2020 | Ross | Brad |
| May 2020 | Jane | Brad |
| June 2020 | Katie | Ross |
| June 2020 | Ross | Brad |
When I'm only working with one month of date, I often use PATH and PATHITEM to recreate the organizational hierarchy to use in filters. But since this data is multiple months, PATH functions won't work because any one person might report to different managers over different months.
Desired output - I'd like to find a way to get one output row that does the same thing as PATH, but calculated the hierarchy for only the row month. Ideally there's some clever filtering I can use with PATH and CALCULATE together so that PATH only takes into account the row month when calculating any given row. It might look like this:
| Month | Employee Name | Supervisory Name | Desired Output |
| April 2020 | Katie | Jane | Brad | Jane | Katie |
| April 2020 | Ross | Brad | Brad | Ross |
| April 2020 | Jane | Brad | Brad | Jane |
| May 2020 | Katie | Jane | Brad | Jane | Katie |
| May 2020 | Ross | Brad | Brad | Ross |
| May 2020 | Jane | Brad | Brad | Jane |
| June 2020 | Katie | Ross | Brad | Ross | Katie |
| June 2020 | Ross | Brad | Brad | Ross |
Appreciate the help!
