Master Transaction Table-Assistance

Hi v-yueyunzh-msft 

Thank you for your reply

I hope the following tables will explain it better.

Master Transaction Table is

Product Transaction Date Order Type Quantity Unit Price Total including service charges
MYT 7/07/2021 Purchase 2400 2.01 4824
MYT 22/12/2021 Sale 2400 2.84 6816
THC 19/04/2020 Purchase 13643 0.35 4775.05
THC 26/04/2020 Purchase 13724 0.29 3979.96
THC 6/06/2022 Sale 27367 0.38 10399.46
NAC 23/02/2022 Sale 1750 5.16 9030
NAC 11/03/2020 Purchase 1200 4.06 4891.95
NAC 12/03/2020 Purchase 550 3.6 1999.95
STC 12/03/2021 Purchase 2000 2 4019.95
STC 18/03/2022 Sale 1000 3 3019.95
STC 16/05/2022 Sale 1000 2.5 2519.95

End Result should be as per the follwing table

Product Quantity (bought) Date of purchase Unit Cost Price Total purchase cost Quantiy (Sold) Date of Sale Unit Sale Price Total Sale Price Profit/Loss Bonus (Quantity *0.001)
MYT 2400 7/07/2021 $2.010 $4,843.950 2400 24/12/2021 $2.840 $6,796.050 $1,952.100 $0.000
NAC 1200 11/03/2020 $4.060 $4,891.950 1200 23/02/2022 $5.160 $6,172.050 $1,280.100 $12.000
NAC 550 12/03/2020 $3.600 $1,999.950 550 23/02/2022 $5.160 $2,838.000 $838.050 $5.500
THC 13643 19/04/2020 $0.350 $4,795.000 13643 6/06/2022 $0.380 $5,164.390 $369.390 $136.430
THC 13724 26/04/2020 $0.290 $3,999.910 13724 6/06/2022 $0.380 $5,215.120 $1,215.210 $137.240
STC 1000 12/03/2021 $2.000 $2,009.98 1000 18/03/2022 $3.000 $3,019.95 $1,009.98 $10.000
STC 1000 12/03/2021 $2.000 $2,009.98 1000 16/05/2022 $2.500 $2,519.95 $509.98 $10.000

Thank you for looking into it