Forum Discussion
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 work as I expected them too. I wrapped them in a FILTER inside a CALCULATE function but it still doen't seem to work.
Below is my sample data:
| Order ID | Order Item ID | Qty |
| 100021 | 0001 | 32 |
| 100021 | 0002 | 32 |
| 100036 | 0002 | 10 |
| 100044 | 0001 | 28 |
| 100044 | 0002 | 28 |
| 100044 | 0003 | 28 |
| 100044 | 0004 | 28 |
| 100044 | 0005 | 28 |
| 100100 | 0001 | 49 |
| 100100 | 0002 | 49 |
| 100222 | 0003 | 108 |
| 100223 | 0001 | 87 |
| 100223 | 0010 | 87 |
| 100270 | 0001 | 10 |
| 100272 | 0001 | 16 |
Below is my expected result:
| Order ID | Order Item ID | Qty | Calculated Col_Qty |
| 100021 | 0007 | 32 | 32 |
| 100021 | 0009 | 32 | |
| 100036 | 0002 | 10 | 10 |
| 100044 | 0001 | 28 | 28 |
| 100044 | 0002 | 28 | |
| 100044 | 0003 | 28 | |
| 100044 | 0004 | 28 | |
| 100044 | 0005 | 28 | |
| 100100 | 0003 | 49 | 49 |
| 100100 | 0005 | 49 | |
| 100222 | 0003 | 108 | 108 |
| 100223 | 0005 | 87 | 87 |
| 100223 | 0010 | 87 | |
| 100270 | 0001 | 10 | 10 |
| 100272 | 0001 | 16 | 16 |
I can't really depend on the Order Item ID because sometimes, it doesn't start with "0001".
My goal is to check if the current data row of Order ID is the same with the previous row. If it is the same, set the Qty value as blank. If they are not the same, retain the Qty value.
Thank you!
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 TeamIf 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!
1 Reply
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 TeamIf 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!