Forum Discussion

frankhofmans's avatar
frankhofmans
Helper IV
2 years ago
Solved

Previous ID in table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
amitchandak
Super User
2 years ago

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