Return text value from a previous row that contains the same value in another column

[chart]

For each Ticket, I want the priorowner column to show who the Owner was in the same ticket's previous modification row. For example, for ticket 128009, the first priorowner cell would be blank because there was no previous Owner. The next 2 rows for 128009 would show Level 2 Team, then Jane Doe, then Peter Parker, etc. I filled in the values below in priorowner for ticket 128009 that I would want to see. I created an index row so that I could order the modifications for each ticket and I know I need to filter somehow to return values for each unique ticket, but I am stuck!

I spent ages getting to this point, but realized that calculate max is only for numerical values, not text: 

priorowner = IF( 'Table1'[diff row] <> 0, CALCULATE(Max(Table1[Owner]),FILTER(Table1,Table1[TicketCalc] =EARLIER(Table1[TicketCalc])&&Table1[Index]<EARLIER(Table1[Index]))))

Please help!

Here is a section of my dataset:

modifiedon	Ticket	Index	Owner	priorowner
2/16/2022 10:38	128009	9269	Level 2 Team
2/15/2022 18:43	127984	9230	Level 2 Team
2/16/2022 8:43	127984	9244	Level 2 Team
2/16/2022 18:35	128009	9422	Level 2 Team	Level 2 Team
2/16/2022 8:42	127984	9243	Jane Doe
2/16/2022 16:02	128009	9384	Jane Doe	Level 2 Team
2/15/2022 19:04	127984	9231	John Doe
2/16/2022 9:20	127984	9250	John Doe
2/16/2022 18:37	128009	9423	Peter Parker	Jane Doe
2/15/2022 19:05	127984	9232	Level 3 Team
2/16/2022 10:49	128009	9273	Level 3 Team	Peter Parker
2/8/2022 13:07	127219	8498	Level 3 Team
2/8/2022 13:48	127219	8516	Bruce Banner
2/8/2022 17:04	127219	8563	Tony Stark

[lbendlin · Accepted solution]

Here is a Power Query version:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHNasMwEIRfRegcyGq1+j2GnEIPgeYWfDBlodBWBpME8va1XDtBitOcVof5NDO7x6PEtbJrBEShIGovV1KhBwjDI6DN440v/C1QHLj9kc1qRMyE+Eh6RFzwNCIaniCTyyNB9D8xmGhT5CLElyZYmWTTXZtYbDuuDWwELAz0SNXyuXSIQFVpleXdZ1r4PUSEUm3guTqXdVXZHH3PJ+7Fvu2/uF/IY6o89/3oxZVCpFCe2ullxE+EjvCXy6HKhKfgXxHkC8IoO4xNf/5gsWlTujWZCXdb7EzYnOrQpat4Pw3dZdP8Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [modifiedon = _t, Ticket = _t, Index = _t, Owner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"modifiedon", type datetime},  {"Index", Int64.Type}, {"Owner", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Index", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "PriorOwner", each try Table.SelectRows(#"Sorted Rows",(k)=> k[Ticket]=[Ticket] and k[Index]<[Index]){0}[Owner] otherwise null)
in
    #"Added Custom"

and here is the same in a DAX calculated column:

Prior Owner = 
var i = 'Table'[Index]
var pi = CALCULATE(max('Table'[Index]),ALLEXCEPT('Table','Table'[Ticket]),'Table'[Index]<i)
return CALCULATE(max('Table'[Owner]),ALLEXCEPT('Table','Table'[Ticket]),'Table'[Index]=pi)

[chart]

Thank you so much! That worked perfectly!