Master Transaction Table-Assistance

[sa100]

Hi, i have the following master table (example

My aim is to get the followings:

-Profit and loss for each product trade. it should include bonus also

-I get a bonus (number of purchase items * 0.10) if the difference between sale and purchase date is more than one year. For example CHW product (purchase item=2304) bought on 31/03/2020 and sold on 31/03/2022. I am getting $230.04 bonus on this. I want to include that into profit/loss for that particular trade.

I tried few different ways, but could not get the desired results. Please assist.

Product	Order Type	Trade Date	Price	Quantity	Service Charges	GST	Total
SPL	Buy	17/06/2020	2.06	4775	18.14	1.81	9856.45
TSB	Buy	15/06/2022	0.31	32177	22.68	2.27	9999.82
TSB	Sell	6/06/2022	0.38	27367	18.14	1.81	10379.51
ZIA	Buy	19/05/2022	0.59	8440	18.14	1.81	4999.55
TRL	Buy	12/05/2022	1.23	4048	18.14	1.81	4998.99
TSB	Buy	26/04/2022	0.29	13724	18.14	1.81	3999.91
TSB	Buy	19/04/2020	0.35	13643	18.14	1.81	4795
BEW	Sell	19/04/2022	5.87	1760	18.14	1.81	10311.25
TRL	Buy	14/04/2022	1.665	3000	18.14	1.81	5014.95
OTS	Sell	7/04/2022	8	682	22.68	2.27	5431.05
BEW	Buy	31/03/2022	5.6396	1760	18.14	1.81	9945.59
CHW	Sell	31/03/2022	4.182	2304	18.14	1.81	9615.49
CHW	Buy	31/03/2020	4.3	2304	18.14	1.81	9927.15

[v-yueyunzh-msft]

Hi , @sa100 

According to your description, you want to get the bonus if the difference between sale and purchase date is more than one year.I have some questions about your need:

(1)If one of your products contains multiple buy/sell records, which one is the standard?
(2)Whether the final purchase item used is buy or sellQuantity?

(3)You just want to add a calculated column or a measure?

Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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

[sa100]

My target is to get a profit/loss column. it should calculate profit or loss for each transaction. 

Master table contains multiples entries of purchase and selling transactions of same product (with different quantity or unit price or date)

Profit/loss should include bonus.

[v-yueyunzh-msft]

Hi , @sa100 

I do not fully understand how to calculate the profit/loss in logic. And the bonus only from if the difference between sale and purchase date is more than one year. But in sample data , one product have more than one buy records.

How can you compare the " if the difference between sale and purchase date is more than one year."?Can you share the end result as a table from your sample data table to us and explain your need detailed?

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

[sa100]

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

[v-yueyunzh-msft · Accepted solution]

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

[v-yueyunzh-msft]

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

[sa100]

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.

[lbendlin]

For example CHW product (purchase item=2304) bought on 31/03/2020 and sold on 31/03/2022. 

according to your sample data it is the other way round?

[sa100]

correction done, thank you