Forum Discussion
MQudmani
4 years agoRegular Visitor
FIFO Cost Valuation for The sales transactions
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 multi...
kirete17
3 years agoFrequent Visitor
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