cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

add data from previous row

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 IDID Open & ResassignmentActivityStart dateEnd dateIndex
I-00110Open03.03.2020 15:00:016.03.2020 16:00:001
I-0010Status Change03.03.2020 15:00:0003.03.2020 15:00:002
I-0010Assignment03.03.2020 15:00:0003.03.2020 15:00:003
I-0010Operator update08.03.2020 16:37:0016.03.2020 16:00:004
I-00111Reassignment16.03.2020 16:00:0025.03.2020  08:05:005
I-0010Status Change18.03.2020 07:10:0018.03.2020 07:10:006

 

Thanks a lot in Advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: add data from previous row

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

View solution in original post

3 REPLIES 3
Highlighted
Solution Specialist
Solution Specialist

Re: add data from previous row

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.

Highlighted
Frequent Visitor

Re: add data from previous row

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.

Highlighted
Super User V
Super User V

Re: add data from previous row

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

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors