Refer to previous row based on conditions from two other columns.

[makarama]

I have something like this:

Ticket Number	Team	Start	End	Column that I want to calculate
105	Liverpool	1-6-2023 02:33	1-6-2023 05:01	-
105	Ajax	1-6-2023 05:01	1-6-2023 07:51	Liverpool - Ajax
113	Ajax	1-6-2023 09:01	1-6-2023 09:14	-
103	Arsenal	1-6-2023 07:49	1-6-2023 09:23	-
103	Ajax	1-6-2023 09:23	1-6-2023 09:35	Arsenal - Ajax
103	Liverpool	1-6-2023 09:35	1-6-2023 10:35	Ajax - Liverpool

This table shows the reassignment that took place for specific tickets. As you can see ticket 105 was handled by 2 teams: Liverpool and Ajax, ticket 113 only Ajax etc.

What I want, is to add a column in this table that will contain the from which and to which team the reassignment took place.

As you can see Liverpool handled the ticket 105 between 1-6-2023 02:33 and 1-6-2023 05:01 and then Ajax took over. So the new column that I want to add would have Liverpool - Ajax as a value. If a ticket was handled by only a single team then no need to calculate something (-). 

So I need a formula to calculate for the same ticket and check the timestamps to make sure we follow the correct order of the reassignment from one team to another.

The resulted column would look like the last column in the table (Column that I want to calculate).

The original table is way larger than this and it contains more than a thousands of tickets. So we need something that can be used for the entire table.

Power Query is preferred.

Thanks

[Greg_Deckler · Accepted solution]

@makarama Not sure of how to do it in Power Query although this DAX might provide some guidance on how to do it in PQ:

Column =
  VAR __TicketNumber = [Ticket Number]
  VAR __Team = [Team]
  VAR __Start = [Start]
  VAR __PrevTeam = MAXX( FILTER( 'Table', [Ticket Number] = __TicketNumber && [End] = __Start ), [Team] )
  VAR __Result = __PrevTeam & " - " & __Team
RETURN
  __Result