Calculate purchase price of last n transacrtions

[Daringg]

Hello,

I have this buy and sale table, i want to calculate the profit per each sale, by getting the buy prices until the sale volume is reached:

The profit at the last record should be:

40*3.75 - (20*25+10*3+10*2)

Any ideas how can I achieve this in Power Query?

Regards,
Darin

[Daringg]

Thanks for your prompt reply lbendlin!

But it is not a loss, because the sell is for 40 pieces only, while the purchase is for 70. Which leads me to identifying a problem in my question/example:

the purchase price of the sold intems should be calculated "First In First Out", so in my example:

we are slling 40*3.75 for which as a purchase price we should look for the firs 40 that are bought, and those are 40*2. And so for this transaction the profit should be 150 - 80...

But how do we calculate the next sale if it is for 15 pieces: it then should identify that the first purchase has been sold so the next 15 should cost 10 * 3.00 and 5 * 2.50...

[lbendlin]

You have two buys on the same day. You will need to provide an index so that these items can be correctly attributed chronologically.

Now what should happen if you have another Sell for 30 units?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

I would approach this by atomizing all buys , each with their buy price, and then using a FIFO buffer to calculate the value that is consumed by each sale.

[Daringg]

Sorry for my late answer, and I realy apretiate your help!

Here is a deeper example:

and here are the tree formulas of cells K6, K7 & K8:

I just can figure how can this be acomplished 😞

Regards,

Darin

[lbendlin]

You already have the total amount. You should be able to use 150 - (80+30+50)  which would result in a loss of 10.

Do you need to worry about exchange rates?