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_dateOrder_IDClient_ID
01-04-20231C01
02-04-20232C02
05-04-20233C03
06-04-20234C01
07-04-20235C02
07-04-20236C02
08-04-20237C03
09-04-20238C03
10-04-20239C01

 

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_dateOrder_IDClient_IDPrevious_Order_ID
01-04-20231C01 
02-04-20232C02 
05-04-20233C03 
06-04-20234C011
07-04-20235C022
07-04-20236C025
08-04-20237C033
09-04-20238C037
10-04-20239C014

 

Can anyone help me with this question?

 

Thanks in advance,

 

Regards, Frank

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.

2 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.