Fetch previous and next row based on column value

[aeriksen]

Hi

I have a table where I want to create two new columns with the time stamp of the next and previous status change of fk_order_id. It's easily solvable in DAX, but I would like to do this in M to use the data ready to go in a dataflow.

I have solved this in M also, but the solutions I've come up with so far are not optimal as there are 100000+ rows and daily refreshes.

Solutions so far in M:
Sorting on Order ID and time stamp and adding two index columns starting with 0 and 1. Merging based on index columns and using conditinal column to fetch previous row if fk_order_id are the same. 

Merging the status change ID with itself, expanding the table keeping only the previous status change ID, then mergin the previous status change ID with the table again to fetch names, time stamps etc. 

I figure there must be an easier way to do this, an equivalent to the EALIER-function in DAX?

DAX expression for fetching next status change:
MINX(
FILTER('FACT Statusendringer',
'FACT Statusendringer'[fk_order_id] = EARLIER('FACT Statusendringer'[fk_order_id])
&& 'FACT Statusendringer'[created] > EARLIER('FACT Statusendringer'[created])),
'FACT Statusendringer'[created])

DAX expression for fetching previous status change:
MAXX(
FILTER('FACT Statusendringer',
'FACT Statusendringer'[fk_order_id] = EARLIER('FACT Statusendringer'[fk_order_id])
&& 'FACT Statusendringer'[created] < EARLIER('FACT Statusendringer'[created])),
'FACT Statusendringer'[created])

Here is an example of the data:

[v-stephen-msft · Accepted solution]

Hi @aeriksen ,

According to your DAX, I think you want to group by the ID column to get the value of the previous or next row. The following is the method in Power Query.

1.Refer to this article to get a ranking by category. And copy the original table to get the second table, the first table sorting starts from 2, and the second table sorting starts from 1.

2.Then merge two tables.

3.Expand the table

What the example shows is to get the next row. If you want to get the previous row, just change the rank order.

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aeriksen]

This is really great! Thank you! 🙂