Previous ID in table

[frankhofmans]

hi PBI experts,

i have a order number table with the following columns:

Order_date	Order_ID	Client_ID
01-04-2023	1	C01
02-04-2023	2	C02
05-04-2023	3	C03
06-04-2023	4	C01
07-04-2023	5	C02
07-04-2023	6	C02
08-04-2023	7	C03
09-04-2023	8	C03
10-04-2023	9	C01

I want to add a column "Previous_order_ID" to the table. An earlier function based on the earlier doesn't work because i can have multiple orders on the same date for the same client (and i don't have a date time, only the day). So i want the following outcome:

Order_date	Order_ID	Client_ID	Previous_Order_ID
01-04-2023	1	C01
02-04-2023	2	C02
05-04-2023	3	C03
06-04-2023	4	C01	1
07-04-2023	5	C02	2
07-04-2023	6	C02	5
08-04-2023	7	C03	3
09-04-2023	8	C03	7
10-04-2023	9	C01	4

Can anyone help me with this question?

Thanks in advance,

Regards, Frank

[v-kongfanf-msft · Accepted solution]

Hi @frankhofmans ,

You can try calculated column like below:

Previous_Order_ID = 
VAR CurrentOrderDate = 'YourTableName'[Order_date]
VAR CurrentClientID = 'YourTableName'[Client_ID]
VAR CurrentOrderID = 'YourTableName'[Order_ID]
VAR a =
    CALCULATE (
        MAX ( 'YourTableName'[Order_ID] ),
        FILTER (
            'YourTableName',
            'YourTableName'[Client_ID] = CurrentClientID
                && 'YourTableName'[Order_date] < CurrentOrderDate
        )
    )
VAR table_ =
    FILTER (
        ALL ( 'YourTableName' ),
        'YourTableName'[Client_ID] = CurrentClientID
            && 'YourTableName'[Order_date] = CurrentOrderDate
            && 'YourTableName'[Order_ID] < CurrentOrderID
    )
VAR b =
    CALCULATE ( MAX ( 'YourTableName'[Order_ID] ), table_ )
RETURN
    IF ( COUNTROWS ( table_ ) > 0, b, a )

Best Regards,
Adamk Kong

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

[amitchandak]

@frankhofmans , Try a new column like

Maxx(filter(Table, Table[Client_ID] = earlier(Table[Client_ID]) && Table[Order_date] <= Earlier(Table[Order_date]) && Table[Order_ID] < Earlier(Table[Order_ID])), Table[Order_date])

Also, you can add sub category index in power query , so that you can use that in place of Date and Order no

Power BI and Power Query- Sub Category Rank and Index: https://youtu.be/tMBviW4-s4A