topic add data from previous row in DAX Commands and Tips

add data from previous row

KatjafromHaj — Sat, 06 Jun 2020 12:51:52 GMT

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!

Re: add data from previous row

stevedep — Sat, 06 Jun 2020 13:02:30 GMT

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.

Re: add data from previous row

KatjafromHaj — Sat, 06 Jun 2020 13:32:25 GMT

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.

Re: add data from previous row

lbendlin — Sat, 06 Jun 2020 15:25:14 GMT

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