topic Separate profit calculation for each transaction in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3697915#M143809 <P>Hello,</P><P>I'd like to create a sample Profit table using the attached Sample Sales and Purchase Table. (see attached tables)</P><P>Calculation Criteria:</P><P>Each sales transaction requires an individual profit calculation.</P><P>Profit calculation will be based on the earliest purchase of the corresponding product.</P><P>If the quantities don't match, combine or partially combine the two purchase transactions.</P><P>I would greatly appreciate your assistance in solving this puzzle. Please note that the sample data includes only two products, but in a real scenario, there may be more than 100 products.</P><P>&nbsp;</P><P>Purchase Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Unit Price</TD><TD>Quantity</TD><TD>Brokerage</TD><TD>GST</TD><TD>PurchaseCost</TD></TR><TR><TD>KLM</TD><TD>217151684</TD><TD>Friday, 26 November 2021</TD><TD>0.37</TD><TD>11555</TD><TD>18.14</TD><TD>1.81</TD><TD>4295.3</TD></TR><TR><TD>KLM</TD><TD>217246886</TD><TD>Friday, 3 December 2021</TD><TD>0.37</TD><TD>13459</TD><TD>18.14</TD><TD>1.81</TD><TD>4999.78</TD></TR><TR><TD>KLM</TD><TD>217735322</TD><TD>Friday, 21 January 2022</TD><TD>0.445</TD><TD>11191</TD><TD>18.14</TD><TD>1.81</TD><TD>4999.95</TD></TR><TR><TD>KLM</TD><TD>217741345</TD><TD>Friday, 21 January 2022</TD><TD>0.43</TD><TD>11717</TD><TD>18.14</TD><TD>1.81</TD><TD>5058.26</TD></TR><TR><TD>KLM</TD><TD>217802780</TD><TD>Tuesday, 25 January 2022</TD><TD>0.39</TD><TD>7641</TD><TD>18.14</TD><TD>1.81</TD><TD>2999.94</TD></TR><TR><TD>KLM</TD><TD>218207447</TD><TD>Monday, 28 February 2022</TD><TD>0.505</TD><TD>9764</TD><TD>18.14</TD><TD>1.81</TD><TD>4950.77</TD></TR><TR><TD>KLM</TD><TD>218433664</TD><TD>Thursday, 17 March 2022</TD><TD>0.5075</TD><TD>5676</TD><TD>18.14</TD><TD>1.81</TD><TD>2900.52</TD></TR><TR><TD>SDA</TD><TD>29254794</TD><TD>Friday, 20 March 2020</TD><TD>0.155</TD><TD>16533</TD><TD>18.14</TD><TD>1.81</TD><TD>2582.57</TD></TR><TR><TD>SDA</TD><TD>22</TD><TD>Monday, 18 January 2021</TD><TD>0.36</TD><TD>2176</TD><TD>0</TD><TD>0</TD><TD>783.36</TD></TR><TR><TD>SDA</TD><TD>213276727</TD><TD>Friday, 29 January 2021</TD><TD>0.915</TD><TD>2175</TD><TD>18.14</TD><TD>1.81</TD><TD>2010.08</TD></TR><TR><TD>SDA</TD><TD>219474324</TD><TD>Friday, 17 June 2022</TD><TD>2.06</TD><TD>4775</TD><TD>18.14</TD><TD>1.81</TD><TD>9856.45</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Sales Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Unit Price</TD><TD>Quantity</TD><TD>Brokerage</TD><TD>GST</TD><TD>Total Sale Price</TD></TR><TR><TD>KLM</TD><TD>17425649</TD><TD>22-Dec-21</TD><TD>0.42</TD><TD>25014</TD><TD>18.14</TD><TD>1.81</TD><TD>10485.93</TD></TR><TR><TD>KLM</TD><TD>17736004</TD><TD>21-Jan-22</TD><TD>0.44</TD><TD>11191</TD><TD>18.14</TD><TD>1.81</TD><TD>4904.09</TD></TR><TR><TD>KLM</TD><TD>18081875</TD><TD>17-Mar-22</TD><TD>0.55</TD><TD>10000</TD><TD>18.14</TD><TD>1.81</TD><TD>5480.05</TD></TR><TR><TD>KLM</TD><TD>18095283</TD><TD>18-Mar-22</TD><TD>0.54</TD><TD>9358</TD><TD>18.14</TD><TD>1.81</TD><TD>5033.37</TD></TR><TR><TD>SDA</TD><TD>14227929</TD><TD>7-Apr-21</TD><TD>1.16</TD><TD>10000</TD><TD>18.14</TD><TD>1.81</TD><TD>11580.05</TD></TR><TR><TD>SDA</TD><TD>14339119</TD><TD>16-Apr-21</TD><TD>1.295</TD><TD>6530</TD><TD>22.68</TD><TD>2.27</TD><TD>8431.4</TD></TR><TR><TD>SDA</TD><TD>18573439</TD><TD>30-Mar-22</TD><TD>3.23</TD><TD>4354</TD><TD>18.14</TD><TD>1.81</TD><TD>14043.47</TD></TR></TBODY></TABLE><P>Profit Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Quantity</TD><TD>Total Sale Price</TD><TD>Purcahse Table Confirmation Number</TD><TD>Purchase Table Quantity</TD><TD>Purchase Cost</TD><TD>Overall Purchase Cost</TD><TD>Profit/Loss</TD></TR><TR><TD>KLM</TD><TD>17425649</TD><TD>22-Dec-21</TD><TD>25014</TD><TD>10485.93</TD><TD>(217151684,217246886)</TD><TD>(11555+13459)</TD><TD>(4295.3+4999.78)</TD><TD>9295.08</TD><TD>1190.85</TD></TR><TR><TD>KLM</TD><TD>17736004</TD><TD>21-Jan-22</TD><TD>11191</TD><TD>4904.09</TD><TD>217735322</TD><TD>11191</TD><TD>4999.95</TD><TD>4999.95</TD><TD>-95.86</TD></TR><TR><TD>KLM</TD><TD>18081875</TD><TD>17-Mar-22</TD><TD>10000</TD><TD>5480.05</TD><TD>(part of 217741345)</TD><TD>10000</TD><TD>(0.43*1000+18.14+1.81)</TD><TD>4319.95</TD><TD>1160.1</TD></TR><TR><TD>KLM</TD><TD>18095283</TD><TD>18-Mar-22</TD><TD>9358</TD><TD>5033.37</TD><TD>(part of 217741345, 217802780)</TD><TD>(1717+7641)</TD><TD>(0.43*1717+0+0)+2999.94</TD><TD>3,738.25</TD><TD>1295.12</TD></TR><TR><TD>SDA</TD><TD>14227929</TD><TD>7-Apr-21</TD><TD>10000</TD><TD>11580.05</TD><TD>part of 29254794</TD><TD>10000</TD><TD>(10000*0.155+18.14+1.81)</TD><TD>1569.95</TD><TD>10010.1</TD></TR><TR><TD>SDA</TD><TD>14339119</TD><TD>16-Apr-21</TD><TD>6530</TD><TD>8431.4</TD><TD>(part of 29254794)</TD><TD>6530</TD><TD>(6530*0.155+0+0)</TD><TD>1012.15</TD><TD>7419.25</TD></TR><TR><TD>SDA</TD><TD>18573439</TD><TD>30-Mar-22</TD><TD>4354</TD><TD>14043.47</TD><TD>(part of 29254794,22,213276727)</TD><TD>(3+2176+2175)</TD><TD>(3*0.155+0+0)+783.36+2010.08</TD><TD>2793.91</TD><TD>11249.56</TD></TR></TBODY></TABLE> Wed, 14 Feb 2024 04:07:55 GMT sa100 2024-02-14T04:07:55Z Separate profit calculation for each transaction https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3697915#M143809 <P>Hello,</P><P>I'd like to create a sample Profit table using the attached Sample Sales and Purchase Table. (see attached tables)</P><P>Calculation Criteria:</P><P>Each sales transaction requires an individual profit calculation.</P><P>Profit calculation will be based on the earliest purchase of the corresponding product.</P><P>If the quantities don't match, combine or partially combine the two purchase transactions.</P><P>I would greatly appreciate your assistance in solving this puzzle. Please note that the sample data includes only two products, but in a real scenario, there may be more than 100 products.</P><P>&nbsp;</P><P>Purchase Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Unit Price</TD><TD>Quantity</TD><TD>Brokerage</TD><TD>GST</TD><TD>PurchaseCost</TD></TR><TR><TD>KLM</TD><TD>217151684</TD><TD>Friday, 26 November 2021</TD><TD>0.37</TD><TD>11555</TD><TD>18.14</TD><TD>1.81</TD><TD>4295.3</TD></TR><TR><TD>KLM</TD><TD>217246886</TD><TD>Friday, 3 December 2021</TD><TD>0.37</TD><TD>13459</TD><TD>18.14</TD><TD>1.81</TD><TD>4999.78</TD></TR><TR><TD>KLM</TD><TD>217735322</TD><TD>Friday, 21 January 2022</TD><TD>0.445</TD><TD>11191</TD><TD>18.14</TD><TD>1.81</TD><TD>4999.95</TD></TR><TR><TD>KLM</TD><TD>217741345</TD><TD>Friday, 21 January 2022</TD><TD>0.43</TD><TD>11717</TD><TD>18.14</TD><TD>1.81</TD><TD>5058.26</TD></TR><TR><TD>KLM</TD><TD>217802780</TD><TD>Tuesday, 25 January 2022</TD><TD>0.39</TD><TD>7641</TD><TD>18.14</TD><TD>1.81</TD><TD>2999.94</TD></TR><TR><TD>KLM</TD><TD>218207447</TD><TD>Monday, 28 February 2022</TD><TD>0.505</TD><TD>9764</TD><TD>18.14</TD><TD>1.81</TD><TD>4950.77</TD></TR><TR><TD>KLM</TD><TD>218433664</TD><TD>Thursday, 17 March 2022</TD><TD>0.5075</TD><TD>5676</TD><TD>18.14</TD><TD>1.81</TD><TD>2900.52</TD></TR><TR><TD>SDA</TD><TD>29254794</TD><TD>Friday, 20 March 2020</TD><TD>0.155</TD><TD>16533</TD><TD>18.14</TD><TD>1.81</TD><TD>2582.57</TD></TR><TR><TD>SDA</TD><TD>22</TD><TD>Monday, 18 January 2021</TD><TD>0.36</TD><TD>2176</TD><TD>0</TD><TD>0</TD><TD>783.36</TD></TR><TR><TD>SDA</TD><TD>213276727</TD><TD>Friday, 29 January 2021</TD><TD>0.915</TD><TD>2175</TD><TD>18.14</TD><TD>1.81</TD><TD>2010.08</TD></TR><TR><TD>SDA</TD><TD>219474324</TD><TD>Friday, 17 June 2022</TD><TD>2.06</TD><TD>4775</TD><TD>18.14</TD><TD>1.81</TD><TD>9856.45</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Sales Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Unit Price</TD><TD>Quantity</TD><TD>Brokerage</TD><TD>GST</TD><TD>Total Sale Price</TD></TR><TR><TD>KLM</TD><TD>17425649</TD><TD>22-Dec-21</TD><TD>0.42</TD><TD>25014</TD><TD>18.14</TD><TD>1.81</TD><TD>10485.93</TD></TR><TR><TD>KLM</TD><TD>17736004</TD><TD>21-Jan-22</TD><TD>0.44</TD><TD>11191</TD><TD>18.14</TD><TD>1.81</TD><TD>4904.09</TD></TR><TR><TD>KLM</TD><TD>18081875</TD><TD>17-Mar-22</TD><TD>0.55</TD><TD>10000</TD><TD>18.14</TD><TD>1.81</TD><TD>5480.05</TD></TR><TR><TD>KLM</TD><TD>18095283</TD><TD>18-Mar-22</TD><TD>0.54</TD><TD>9358</TD><TD>18.14</TD><TD>1.81</TD><TD>5033.37</TD></TR><TR><TD>SDA</TD><TD>14227929</TD><TD>7-Apr-21</TD><TD>1.16</TD><TD>10000</TD><TD>18.14</TD><TD>1.81</TD><TD>11580.05</TD></TR><TR><TD>SDA</TD><TD>14339119</TD><TD>16-Apr-21</TD><TD>1.295</TD><TD>6530</TD><TD>22.68</TD><TD>2.27</TD><TD>8431.4</TD></TR><TR><TD>SDA</TD><TD>18573439</TD><TD>30-Mar-22</TD><TD>3.23</TD><TD>4354</TD><TD>18.14</TD><TD>1.81</TD><TD>14043.47</TD></TR></TBODY></TABLE><P>Profit Table</P><TABLE border="1"><TBODY><TR><TD>Code</TD><TD>Confirmation Number</TD><TD>Trade Date</TD><TD>Quantity</TD><TD>Total Sale Price</TD><TD>Purcahse Table Confirmation Number</TD><TD>Purchase Table Quantity</TD><TD>Purchase Cost</TD><TD>Overall Purchase Cost</TD><TD>Profit/Loss</TD></TR><TR><TD>KLM</TD><TD>17425649</TD><TD>22-Dec-21</TD><TD>25014</TD><TD>10485.93</TD><TD>(217151684,217246886)</TD><TD>(11555+13459)</TD><TD>(4295.3+4999.78)</TD><TD>9295.08</TD><TD>1190.85</TD></TR><TR><TD>KLM</TD><TD>17736004</TD><TD>21-Jan-22</TD><TD>11191</TD><TD>4904.09</TD><TD>217735322</TD><TD>11191</TD><TD>4999.95</TD><TD>4999.95</TD><TD>-95.86</TD></TR><TR><TD>KLM</TD><TD>18081875</TD><TD>17-Mar-22</TD><TD>10000</TD><TD>5480.05</TD><TD>(part of 217741345)</TD><TD>10000</TD><TD>(0.43*1000+18.14+1.81)</TD><TD>4319.95</TD><TD>1160.1</TD></TR><TR><TD>KLM</TD><TD>18095283</TD><TD>18-Mar-22</TD><TD>9358</TD><TD>5033.37</TD><TD>(part of 217741345, 217802780)</TD><TD>(1717+7641)</TD><TD>(0.43*1717+0+0)+2999.94</TD><TD>3,738.25</TD><TD>1295.12</TD></TR><TR><TD>SDA</TD><TD>14227929</TD><TD>7-Apr-21</TD><TD>10000</TD><TD>11580.05</TD><TD>part of 29254794</TD><TD>10000</TD><TD>(10000*0.155+18.14+1.81)</TD><TD>1569.95</TD><TD>10010.1</TD></TR><TR><TD>SDA</TD><TD>14339119</TD><TD>16-Apr-21</TD><TD>6530</TD><TD>8431.4</TD><TD>(part of 29254794)</TD><TD>6530</TD><TD>(6530*0.155+0+0)</TD><TD>1012.15</TD><TD>7419.25</TD></TR><TR><TD>SDA</TD><TD>18573439</TD><TD>30-Mar-22</TD><TD>4354</TD><TD>14043.47</TD><TD>(part of 29254794,22,213276727)</TD><TD>(3+2176+2175)</TD><TD>(3*0.155+0+0)+783.36+2010.08</TD><TD>2793.91</TD><TD>11249.56</TD></TR></TBODY></TABLE> Wed, 14 Feb 2024 04:07:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3697915#M143809 sa100 2024-02-14T04:07:55Z Re: Separate profit calculation for each transaction https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3700644#M143917 <P><FONT>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="488032" data-lia-user-login="sa100" class="lia-mention lia-mention-user">sa100</a>&nbsp;,</FONT><BR />I can understand what you mean, can you please provide the relationship between the sales table and the purchase table so that we can help you better<BR /><BR /></P> <P style="margin: 0in; font-size: 11.25pt; color: black;"><SPAN>Best regards</SPAN><SPAN>,</SPAN></P> <P style="margin: 0in; font-family: SegoeUI; font-size: 11.25pt; color: black;"><SPAN>Albert He</SPAN></P> Thu, 15 Feb 2024 08:24:58 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3700644#M143917 Anonymous 2024-02-15T08:24:58Z Re: Separate profit calculation for each transaction https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3702568#M143996 <P>Hi Albert&nbsp;Anonymous</a>&nbsp;</P><P>Thanks for looking into this</P><P>Currently the relationship between Sales and Purchase table is many to many (code).</P><P>&nbsp;</P><P>Otherway is to create a Master table by combining the Sales and Purchase table. Then we can have one to one relationship between Sales and Master; and between Purchase and Master (confirmation number)</P><P>Thank you</P><P>Regards</P><P>Sushil Arora</P> Thu, 15 Feb 2024 22:51:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3702568#M143996 sa100 2024-02-15T22:51:14Z Re: Separate profit calculation for each transaction https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3734717#M145560 <P>Hi all, I'm eagerly anticipating guidance or a solution regarding my earlier post. thank you</P> Thu, 29 Feb 2024 21:58:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Separate-profit-calculation-for-each-transaction/m-p/3734717#M145560 sa100 2024-02-29T21:58:01Z