Forum Discussion

sa100's avatar
sa100
Helper I
3 years ago
Solved

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.1...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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's avatar
v-yueyunzh-msft
Community Support
3 years ago

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's avatar
sa100
Helper I
3 years ago

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's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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's avatar
      sa100
      Helper I
      3 years ago

      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's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        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