Find previous state of an order in a large massive

[Zaknafeein]

Hi. 

I have a massive of data what looks like this 

Whole set have a lot of orders, basicly it says who and when set status to an order. 

I want to calculate previous status for each row, if it have it, of course. 

From example the bottom row is a first status, so no previus status. 

Second row from bellow is second status, so it must have 61 as previus status and so on. 

I tryed some variants but non of them, give results ( 

Will be glad for a hint or a solution. 

[tamerj1]

Hi @Zaknafeein 

you can use (calculated column)

LOOKUPVALUE(Table[Status id], Table[Index],Tabel[Index]-1)

Please let me knoow if it works. If so please consider marking my reply as accepted solution. Thank you

[Zaknafeein]

Hi, unfortunately no, because it takes last status of previous order as last status in first status of new. But i found solution what works thank you for answer, it helped to find the solution

[lbendlin · Accepted solution]

It is great that your data alredy has an index column. That makes it so much easier.  Slap an actual index on that columns (via Table.AddKey), maybe add a Table.Buffer for good measure  and then you can run a small piece of Power Query code to lookup the prior data.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.

[Zaknafeein]

Hi, thanks for an advice it help me to understand that my raw data was not prepared correctly. I have sorted data only by date and time column before i add an index. And it din an effect that one order have not have cohirent index order and one Order id can have a gap in meadle of it index.

So i add additional step of sorting in Power Query. At firs i sordet all order id and only then date time colunmn. And then i have add an index.

So order id is grooped in claster and have right order of index from earliest to latest.

And them i add this dax:

Last Status = 
CALCULATE (
    VALUES ('OrderHistory'[Status] ),
    FILTER (
        ALL ( 'OrderHistory' ),
        'OrderHistory'[Index]
            = EARLIER ( 'OrderHistory'[Index] ) -1
            && 'OrderHistory'[Order Id]=EARLIER(OrderHistory[Order Id]
            
    )

))