Calculating Purchasing Costs with FIFO

[Andreas_029]

Hi

I'm trying to calculate purchasing costs for individual sales using FIFO (First In/First Out).

I have two tables, one with all our sales:

and a table with all stock entries:

Example:

I want to calculate the purchasing costs for order no. 631326. On May 18 just before the sale, we had 16 pieces on stock, 12 of which were purchased on May 15h at a price of 264.00, 4 of them on April 17 at a price of 240.00. The calculation with FIFO is therefore like this:

4 x 240.00

8 x 264.00

----------

3072.00 (256.00/per piece)

How can I calculate this in Power Bi? I am stuck how to do this, any help or lead to get this done, would be great

Many thanks

Andreas

[v-yuezhe-msft]

@Andreas_029,

Please check if DAX in the PBIX file below returns your expected result.

https://1drv.ms/u/s!AhsotbnGu1NolBOP2J2oguvUb_Zj

Regards,
Lydia

[pers]

Hi, the drive PBIX isnt available - could the same be shared again please, as I am also trying to get FIFO implementation worked.

[Andreas_029]

Hi Lydia

Many thanks for your help. That's a great start for me

However, I’m not quite sure if I understand P1 and P2. I think what it does is simply compare quantities, but not a real FIFO calculation (https://www.thebalancesmb.com/fifo-inventory-cost-method-explained-398266)

What it would need to be is the quantity of the last two stock entries, starting with the older one. In this case, I believe for FIFO it would be like this:  

P2 = Current Stock - Last Stock Entry

P1 = Qty Sold - P2

However, this only works if you calculate with two stock entries. If "QTY sold" is larger than the last two stock entries combined, the stock entry before is needed as well. If the "QTY Sold" is smaller than the last entry then only the last one is needed.

And it's this flexibility in the calculation where I get stuck again.

What I’m after is a measure that goes through all the stock entries until the combined quantity of the stock entries is larger than the current stock. Then calculate the average costs of each entry starting with the oldest. All this of course for a specific article code.

Using the example from above but with changed figures it could go like this for the calculation of the purchasing costs of order no 631326

QTY Sold: 10

Current Stock: 11

As a first step, we need to find out when was the last stock entry of which there are still items on stock

Entry 1 (15.05.2018): 5 pieces @ 264.00

Entry 2 (17.04.2018): 4 pieces @ 240.00

Entry 3 (01.04.2018): 12 pieces @ 200.00

Current Stock minus Entry 1 = 11 - 5 = 6

6 minus Entry 2 = 6 - 4 = 2

2 - minus Entry 3 = 2 - 12 = - 10

Based on that 2 pcs are on stock from Entry 3. Therefore the FIFO calculation would be like this:

2 x 200.00 (entry 3)

4 x 240.00 (entry 2)

4 x 264.00 (entry 1)

————

2416 / 10 = 241.60

I hope this make sense, it would be great if you could help me out here.

Regards

Andreas