cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Community Champion
Community Champion

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
Community Champion
Community Champion

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 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.