Forum Discussion
Master Transaction Table-Assistance
Hi, sa100
You can refer to this .pbix file.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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
Hi , sa100
For your need , you want to calculate the bonus, you can try to refer to :
We can create a measure like this:
Profit/Loss Bonus (Quantity *0.001) =
var _minbuydate=[Date of Sale]
var _currentdate=MAX('Table'[Trade Date])
var _datediff=DATEDIFF(_minbuydate,_currentdate,DAY)
return
IF(MAX('Table'[Order Type])="Sell",IF(_datediff>365,SUM('Table'[Quantity])*0.001,0.000),BLANK())Then we put it on the visual :
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you v-yueyunzh-msft for bonus calculation.
But the main part of my query is-How should I calculate profit and loss as it include split the purchase/sales value in a single row or add the two rows values from Master Transaction Table. Thank you.