FIFO Cost

[PhyuLayKhine333]

Hi All Experts,

I am beginner to BI and tried many methods in order to get the below result and also google about this problem but i did not get the correct answer and have no ideas how to use the relevant formula to get this below result.  

Any suggestion of fomula to get the beblow result?

Thanks and Regards,

Sales Table

Date	Product Name	Quantity
10/10/2022	A	10
10/12/2022	A	3
10/12/2022	A	2
10/13/2022	A	1
10/10/2022	B	22
10/11/2022	B	10
10/12/2022	B	3
10/12/2022	B	3

Purchase Table

Date	Product Name	Quantity	Cost
9/12/2022	A	10	110
9/30/2022	A	4	130
10/1/2022	A	6	150
9/1/2022	B	20	210
9/2/2022	B	20	250
9/20/2022	B	10	230

Then i would like get the table like this;

Date	Product Name	Quantity	Cost of sales	Closing Inventory Qty	Closing Inventory Value
10/10/2022	A	10	1100	10	1420
10/12/2022	A	3	390	7	1030
10/12/2022	A	2	280	5	750
10/13/2022	A	1	150	4	600
10/10/2022	B	22	920	28	6800
10/11/2022	B	10	2500	18	4300
10/12/2022	B	3	750	15	3550
10/12/2022	B	3	750	12	2800

[kirete17 · Accepted solution]

Power Query may not be the best solution, but I made it work

let
    Purchase = Excel.CurrentWorkbook(){[Name="Purchase"]}[Content],
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Custom1 = 
        Table.AddColumn(
            Table.Group( Sales, "Product Name", { "S", each _ } ),
            "P",
            (x)=> List.Select( Table.ToRows( Purchase ), (y)=> y{1} = x[Product Name] )
        ),
    Custom2 = 
        Table.ToList(
            Custom1,
            (t)=>
                let
                    f =
                        (p)=>
                        List.Accumulate(
                            t{2},
                            { 0, 0 },
                            (x,y)=>
                                if x{1} + y{2} < p
                                then { x{0} + y{2} * y{3}, x{1} + y{2} }
                                else { x{0} + List.Max( { 0, p - x{1} } ) * y{3}, x{1} + y{2} }
                        ){0},
                    a =
                        Table.AddIndexColumn( t{1}, "i", 1, 1 ),
                    b =
                        Table.AddColumn( a, "q", each f( List.Sum( Table.FirstN( a, [i] )[Quantity] ) ) ),
                    c =
                        Table.AddColumn( b, "amount", each if [i] = 1 then [q] else [q] - b[q]{[i]-2} ),
                    l =
                        List.Transform( List.Zip( List.Transform( t{2}, (x)=> { x{2}, x{2} * x{3} } ) ), List.Sum ),
                    d =
                        Table.AddColumn( c, "Qty", each l{0} - List.Sum( Table.FirstN( c, [i] )[Quantity] ) ),
                    e =
                        Table.AddColumn( d, "Value", each l{1} - List.Sum( Table.FirstN( c, [i] )[amount] ) )
                in
                    Table.RemoveColumns( e, { "i", "q" } )
        ),
    Custom3 = 
        Table.Combine( Custom2 )
in
    Custom3

[Daryl-Lynch-Bzy]

Hi @PhyuLayKhine333 - it appears you have asked the same question four different forums.  Do you still need a response?