Forum Discussion

crln-blue's avatar
crln-blue
Post Patron
2 years ago
Solved

Calculated Column that checks the previous row

Hello everyone, I'm trying to check the data of the previous row so I can compare it to the current row and apply some logic. I'm aware of the EARLIER and EARLIEST functions but somehow, it doesn't w...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi crln-blue ,

     

    First create an index column and count the number of occurrences of each Order ID.

    Index = RANKX(FILTER(ALL('Order'), 'Order'[Order ID] = EARLIER('Order'[Order ID])), 'Order'[Order Item ID],, ASC, Dense)

     

    Create a calculated column that sets the Qty value to blank if index is not equal to 1. If index = 1, Qty value is retained.

    Calculated Col_Qty = IF('Order'[Index] = 1,'Order'[Qty],BLANK())

     

    Drag columns to the report page for display.

     

    pbix file is attached.

     

    If you have any further questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi crln-blue ,

 

First create an index column and count the number of occurrences of each Order ID.

Index = RANKX(FILTER(ALL('Order'), 'Order'[Order ID] = EARLIER('Order'[Order ID])), 'Order'[Order Item ID],, ASC, Dense)

 

Create a calculated column that sets the Qty value to blank if index is not equal to 1. If index = 1, Qty value is retained.

Calculated Col_Qty = IF('Order'[Index] = 1,'Order'[Qty],BLANK())

 

Drag columns to the report page for display.

 

pbix file is attached.

 

If you have any further questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!