FIFO Cost Valuation for The sales transactions

[Syndicate_Admin]

Hi

I am looking for a way to calculate the cost per sales transaction using FIFO method in Power Query, I tried a lot of formulas but none is working.

In my case, I have multiple items and multiple purchasing costs. I attached a sample of the data so it can help better understand the case.

[Syndicate_Admin]

Hi @MQudmani ,

As a general advice, please, provide:

1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1 to 2.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Syndicate_Admin]

Hi @ERD 

This is the data sample with the expected value as a cost of goods sold column, the calculation for this column was as follows :

for the symbol "100", I first bought 2 pcs for 43.65 each and then we sell 1 pcs so the cost of this 1 pcs is 43.65 after that we bought 1 pcs for 84 then we sell 1 pcs and the cost of this pcs should be 43.65 ( we bought 2 and then sell 1 so we have 1 left), after that, we sell 1 pcs and the cost of this pcs should be 84 ( we finish the first batch of the products that we bought first for 43.65 per item and then we move to the second batch that cost 84 per item),

this case should be repeated for the second item also.

Symbol	Units	type	Total Value	value per unit	index	Cost of goods sold
100	2	Buy	87.30	43.65	1
100	1	Sale	84.02	84.02	2	43.65
100	1	Buy	84.00	84.00	3
100	1	Sale	78.53	78.53	4	43.65
100	1	Sale	92.94	92.94	5	84.00
1525	16	Buy	368.32	23.02	1
1525	100	Buy	2,302.00	23.02	2
1525	3	Buy	69.06	23.02	3
1525	3	Buy	69.06	23.02	4
1525	12	Sale	363.49	30.29	5	276.24
1525	1	Sale	40.99	40.99	6	23.02
1525	4	Sale	119.89	29.97	7	92.08
1525	1	Sale	42.50	42.50	8	23.02
1525	3	Sale	105.00	35.00	9	69.06
1525	1	Sale	40.00	40.00	10	23.02
1525	14	Buy	315.64	22.55	11
1525	3	Sale	120.00	40.00	12	69.06
1525	3	Sale	150.00	50.00	13	69.06
1525	3	Sale	117.00	39.00	14	69.06
1525	1	Sale	45.34	45.34	15	23.02
1525	1	Sale	41.45	41.45	16	23.02
1525	10	Sale	299.72	29.97	17	230.20
1525	2	Sale	79.91	39.96	18	46.04
1525	1	Sale	39.56	39.56	19	23.02
1525	1	Sale	38.11	38.11	20	23.02
1525	1	Sale	35.00	35.00	21	23.02
1525	3	Sale	83.16	27.72	22	69.06
1525	45	Sale	1,125.00	25.00	23	1,035.90
1525	1	Sale	35.00	35.00	24	23.02
1525	2	Sale	80.09	40.04	25	46.04
1525	2	Sale	80.00	40.00	26	46.04
1525	2	Buy	80.00	40.00	27
1525	2	Sale	100.00	50.00	28	46.04
1525	2	Sale	79.99	39.99	29	46.04
1525	1	Sale	39.69	39.69	30	23.02
1525	2	Sale	79.92	39.96	31	46.04
1525	1	Sale	39.99	39.99	32	23.02
1525	2	Buy	80.00	40.00	33
1525	4	Sale	164.02	41.01	34	92.08
1525	2	Sale	75.01	37.51	35	46.04
1525	10	Buy	202.43	20.24	36
1525	9	Sale	353.96	39.33	37	206.23
1525	1	Sale	40.52	40.52	38	22.55
1525	3	Sale	75.00	25.00	39	67.64
1525	5	Sale	138.60	27.72	40	147.64

[Syndicate_Admin]

@MQudmani ,

I'm not sure how to get this in Power Query, but here are some links that might be useful:

• https://community.powerbi.com/t5/Desktop/Power-Qurey-for-FIFO-Stock-Calculation/td-p/312235
• DAX option: https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[Syndicate_Admin]

FYI

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Total Value", type number}, {"value per unit", type number}}),
    Custom1 = 
        Table.Group(
            #"Change Type",
            "Symbol",
            {
                "q",
                each
                    let
                        t = Table.Group( _, "type", { "w", (x)=> x } ),
                        buy = t{0}[w],
                        sale = t{1}[w],
                        f = 
                            (p)=>
                            List.Accumulate(
                                Table.ToRows( buy ),
                                { 0, 0 },
                                (x,y)=>
                                    if x{1} + y{1} < p
                                    then { x{0} + y{1} * y{4}, x{1} + y{1} }
                                    else { x{0} + List.Max( { p - x{1}, 0 } ) * y{4}, x{1} + y{1} }
                            ){0},
                        a =
                            Table.AddIndexColumn( sale, "i", 1, 1 ),
                        b =
                            Table.AddColumn( a, "q", (x)=> f( List.Sum( Table.FirstN( a, x[i] )[Units] ) ) ),
                        c =
                            Table.AddColumn( b, "cost", (x)=> if x[i] = 1 then x[q] else x[q] - b[q]{x[i]-2} ),
                        d =
                            Table.AddColumn( buy, "cost", (x)=> null ) & Table.RemoveColumns( c, { "i", "q" } )
                    in
                        Table.Sort( d, "index" )
            }
        )[q],
   Custom2 = 
        Table.Combine( Custom1 )
in
    Custom2