Calculated Column that checks the previous row

[crln-blue]

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!

[v-huijiey-msft · Accepted solution]

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!