Calculate in dependance to other rows

[zehbart]

Hello,

below you can see a part of a bigger table with about 30.000 rows and many different 'ReceiptNo's. The column 'FinalAmount' is usually simplay calculated by dividing 'Amount' by 100.

However, if the 'UnitType' is 5, the FinalAmount for all rows with the same 'ReceiptNo' and and a Time which is before the time of the row with 'UnitType' 5 should be 0.00.

In this example there is UnitType 5 in row 9. So, the FinalAmount in row 1-8 should be 0.00, since the ReceiptNo is the same and the Time is smaller than in row 9.

Does anyone have an idea how to implement this into the calcualtion of 'FinalAmount'?

Thanks already in advance
zehbart

Time	UnitType	ReceiptNo	Product ID	Amount	FinalAmount
08:28:09	0	57	1245	454	4.54
08:28:09	1	57	340	195	1.95
08:28:09	1	57	311	229	2.29
08:28:09	1	57	325	345	3.45
08:28:09	0	57	315	78	0.78
08:28:09	0	57	395	265	2.65
08:28:09	2	57	0	1566	15.66
08:28:10	6	57	0	-1	-0.01
08:28:26	5	57	0	454	4.54
08:28:29	0	57	1245	454	4.54
08:28:29	1	57	340	195	1.95
08:28:29	1	57	311	229	2.29
08:28:29	1	57	325	345	3.45
08:28:30	0	57	315	78	0.78
08:28:30	0	57	395	265	2.65
08:28:30	2	57	0	1566	15.66
08:28:30	6	57	0	-1	-0.01

 

[p45cal]

In the attached (it would be easier for us if you had attached one) is a formula in column F - in row 2 it's:

=IF(AND(COUNTIFS($B$2:$B$18,5,$C$2:$C$18,C2)>0,A2<MIN(IF((($B$2:$B$18=5)*($C$2:$C$18=C2))=1,$A$2:$A$18))),0,$E2/100)

For your understanding, that formula has been derived from the columns H:J which of course you don't need.

Would there ever be a case where two or more unit types of value 5 would be present for the same receipt number at different times? If so which of the times should be used to bring the Final Amount down to zero? The earliest or the latest?

 

[zehbart]

Thanks, p45cal. Actually the table-part comes directly from a power query table. I am looking for a solution to solve this matter with power query. Sorry for the misunderstanding.

 

[p45cal]

In the attached is a Power Query offering on Sheet1 (2).
It should work fine if there's only one Type 5 per receipt no. but may need a tweak, one way or the other (max/min), so again:

Would there ever be a case where two or more unit types of value 5 would be present for the same receipt number at different times? If so which of the times should be used to bring the Final Amount down to zero? The earliest or the latest?

I've made no attempt to make the Mcode more elegant/shorter.

 

[zehbart]

I tried to break down this issue more into detail. What I would need is custom column in power query that checks if there is any row which with the same ReceiptNo and a time that is bigger than in the actual row. Hope this is more clear now.

 

[p45cal]

see msg#4

 

[zehbart]

that looks great, thank you!

 

[zehbart]

And No, there won't be a case where two or more unit types of value 5 would be present for the same receipt number at different times. Your solution works for me, thanks again.