add data from previous row

[KatjafromHaj]

Hi Community,

I have a list of incidents, with tow major events: Open & Reassignment. The enddate of each event is the respective startdate of the following major event. Between these major events, other "Sub events" happening.  In column " ID Open & Reassignment" I need the ID of the previous major event. 

I tried many approaches but nothing worked so far.

Incident ID	ID Open & Resassignment	Activity	Start date	End date	Index
I-001	10	Open	03.03.2020 15:00:0	16.03.2020 16:00:00	1
I-001	0	Status Change	03.03.2020 15:00:00	03.03.2020 15:00:00	2
I-001	0	Assignment	03.03.2020 15:00:00	03.03.2020 15:00:00	3
I-001	0	Operator update	08.03.2020 16:37:00	16.03.2020 16:00:00	4
I-001	11	Reassignment	16.03.2020 16:00:00	25.03.2020  08:05:00	5
I-001	0	Status Change	18.03.2020 07:10:00	18.03.2020 07:10:00	6

Thanks a lot in Advance!

[stevedep]

Instead of dax you can make a copy of the table and join the 2 with start date = end dat and Id. Merge tables in power query.

[KatjafromHaj]

Hi Steve,

column "End date" is a calculated column, so it is not available in power query.  The original query contais only the start date of each event.

I created 2 queries : 1. all events & 2. Open & Reassignment.

[lbendlin · Accepted solution]

You will want to implement your entire logic in Power Query. There are many tutorials on how to reference other rows in your source data, like here https://www.myonlinetraininghub.com/referencing-next-row-power-query