How to Calculate the Average Purchase Price over Multiple Purchases in Time

[leniorcortivo]

Hello Dear Coleagues,

Need your knwoledge to figure out this calculation. Please see the following transactions.

Code	Date	Operation	Quantity	Unit Price	Cost	Cumulative Quantity	Average Unit Price	Average Unit Price + Cost
ABCD	09/16/2019	Purchase	3	U$ 156,10	U$ 0,13	3	U$ 156,10	U$ 156,14
ABCD	10/15/2019	Purchase	2	U$ 164,40	U$ 0,10	5	U$ 159,42	U$ 159,47
ABCD	01/05/2020	Purchase	1	U$ 171,00	U$ 0,05	6	U$ 161,35	U$ 164,40
ABCD	01/08/2020	Sell	3	U$ 187,36	U$ 0,11	3	U$ 161,35	U$ 161,40
ABCD	01/10/2020	Sell	2	U$ 187,50	U$ 0,11	1	U$ 161,35	U$ 161,40
ABCD	01/15/2020	Sell	1	U$ 187,50	U$ 0,11	0	U$ 0,00	U$ 0,00
ABCD	03/09/2020	Purchase	1	U$ 161,50	U$ 0,04	1	U$ 161,50	U$ 161,54
ABCD	06/30/2020	Purchase	1	U$ 169,45	U$ 0,04	2	U$ 165,48	U$ 165,52
ABCD	01/13/2021	Purchase	3	U$ 160,00	U$ 0,15	5	U$ 162,19	U$ 162,24
ABCD	08/09/2021	Purchase	2	U$ 135,49	U$ 0,15	7	U$ 154,56	U$ 154,62
ABCD	09/01/2021	Purchase	4	U$ 141,03	U$ 0,15	11	U$ 149,64	U$ 149,69
ABCD	09/16/2021	Purchase	2	U$ 141,24	U$ 0,15	13	U$ 148,35	U$ 148,40

The chalenge is to calculate correctly the last column, where over time when you hit 0 in your cumulative quantity column you must "restart" your average unit price for that point forward. When a selling operation is listed, the average unit price must not change.

This is a table with a single code, ABCD, but the same data source could have multiple codes. I just kept simple with one code to be more easier to understand.

The desired result would be a table with a single line for the code ABCD and your current (latest) Average Unit Price.

Thank you!

[Syndicate_Admin]

Good day, you have a solution for this post?, I have the same doubt.

[leniorcortivo]

Hello my friend. Not yet!

[v-jingzhang]

Hi @leniorcortivo 

Can we think that the first 7 columns (Code, Date, Operation ... Cumulative Quantity) are what you already have and the last two columns (Average Unit Price, Average Unit Price + Cost) are what you want to achieve? It is because if you already have all these 9 columns data, you just need to get data from the last line for each code, right?

Best Regards,
Community Support Team _ Jing

[vanessafvg]

so the table you have provided, is that the data that is given to you or are you asking to provide the results in that table.

Not sure if you asking for 2 things here?

create an average based on unit price cost, averaged between 0 and 0?

and then a single line showing the latest?   for a specific code?

what is the desired output in an example, can you show or is it just the last line of the table above?

[leniorcortivo]

The last two columns we want to achive in the calculation. The data is on the example table just to be compared as the correct result that should be achieved in the solution.