Forum Discussion
Master Transaction Table-Assistance
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 |
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
9 Replies
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?
correction done, thank you
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
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.
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
