Hi Everyone,
I would like to calculate the cost of sales per transaction using FIFO method in Power Query. I have attached a sample data set and the required output. What formula in Power Query is required to compute the output in the column highlighted in yellow (see attached dataset).
Best
Harvey
FIFO Cost Valuation for The sales transactions.xlsx (12.7 KB)
Hello @Harvey, a similar topic was covered in this forum thread link. You might get additional tips here Share Valuation using FIFO method
Thanks. It is similar but not quite the solution. Is the FIFO column the cost of sales? What I want to generate is the cost of sales column so that I can track individual product cost over time using the FIFO method.
Hi @Harvey,
Give this a go. Just paste the full code into a new blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS5cgMxCIbfZWsmI0AXbV4hpcdFinRpU+Tto9NCa2OnWLH2fsP1Iy6XA5074KDyvP/8ljMn4GI8QwzF4nGFCWF5Pj6/vyrlwZGydMKGLz8PfuwmZQisrH+MCYF4ZUPHArUM4y0exwzc0uGeFm5gc9tJemNH0EvnrYTO8o2MAi4qjP+H+S0yrVo4MnipLw5I7opZoHcgomzUnF8cokCuAAlIqs20HBKEZfK5junOtT/qIWZm80C3IX4pgQFi/UklWvuGVjxa3u4kmEyon9uBd/2/taHWztLzMBIPwF5ZNHuP4IOyGPdBWiSJQCLVfdzar4RPBcCWIkidFMxG8AKEqCxaOnAGRGXJWeBNUrJkyAztIlEa5WxatCbMRr8hhXF1mkt+HdUbPckO3Jhx57tDC5xDQvGMjF2zCEuBvgDaGFF+opIMlfqlslWKoq174pGU7oy2xy0006tKma2dEMdeLgPs2ohYCqQwgAQBu17nSR8bs+xLz+0FqK30TQjRuvOoVIC5+bZXXCBl2VpKKcxp400PPZblFugBLg26Xv8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Symbol = _t, Units = _t, #"type" = _t, #"Total Value" = _t, #"value per unit" = _t, index = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Symbol", Int64.Type}, {"Units", Int64.Type}, {"Total Value", Currency.Type}, {"value per unit", Currency.Type}, {"index", Int64.Type}}),
GroupRows = Table.Group(ChType, {"Symbol"}, {{"AllRows", each
let
t = Table.AddIndexColumn( _, "Index", 0, 1 ),
lCost = List.Combine( Table.AddColumn( Table.SelectRows( t[[type],[Units],[value per unit]], each ([type] = "Buy")), "Value", each List.Repeat( {[value per unit]}, [Units] ))[Value] ),
AddRecord = Table.AddColumn( t, "Values", each
[
i = [Index],
Sell = List.Sum( Table.SelectRows( t[[type],[Units], [Index]], each ([type] = "Sale" and [Index] <= i))[Units] ),
PrevSell = if [type] = "Sale" then List.Sum( Table.SelectRows( t[[type],[Units], [Index]], each ([type] = "Sale" and [Index] < i))[Units] ) else 0,
Cost of goods sold = let pSell = if PrevSell = null or PrevSell-1 < 0 then 0 else PrevSell in
if [type] = "Sale" then List.Sum( List.FirstN( List.Skip( lCost, pSell ), Sell - pSell )) else null
]
)
in
AddRecord, type table [Symbol=nullable number, Units=nullable number, type=nullable text, Total Value=nullable number, value per unit=nullable number, index=nullable number] }}
)[[AllRows]],
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"Symbol", "Units", "type", "Total Value", "value per unit", "Values"}, {"Symbol", "Units", "type", "Total Value", "value per unit", "Values"}),
GetCost = Table.ExpandRecordColumn(ExpandAllRows, "Values", {"Cost of goods sold"}, {"Cost of goods sold"})
in
GetCost
.
Sample of the result
.
lCost generates a list with costs for each item
Sell and PrevSell is used to select a range of values in that lCost list before summing them up.
I hope this is helpful
Hi @Melissa
Thank you for the solution, everything looks fine. I’m just failing to implement the code on my end. See error message in the attached.
Hi @Harvey,
I think you’ve pasted it directly in the formula bar… Instead open the advanced editor window, select all and then paste in the full script. Just tested it and it’s not giving me any issues.
Hope that helps 
Great, worked!
I wanted to implement this on a larger data set from a data warehouse, how can this be done? Do I just change the source?
Is there a specific reason why your index starts with a zero?
e.g. t = Table.AddIndexColumn( _, “Index”, 0, 1 )
I had started off from 1. Does this make any difference?
Can you also show me the steps to reproduce the columns “sell”, “prevsell” and “cost of goods sold” in power query steps? I am having a hard time combing through the code trying to figure out how each step was arrived at so that I can copy the same on my larger data set.
Thanks
Basically yes. And be aware of changes in columns- / fieldnames.
Lists have a zero based index so that makes it more easy to understand what is going on, however you can omit that of course and just subtract 1 from your non-zero based index to achieve the same
I wrote that Record by hand but let me see if I can clarify what is happening
[
Sell = List.Sum(
Table.SelectRows(
t[[type], [Units], [Index]], each // 1. retrieve table "t" and only bring in columns: type, Units and Index
( [type] = "Sale" and [Index] <= i ) // 2. filter down to type = Sale and Index <= Index from the outer table (=cum. sum)
)[Units] // 3. Extract the units list from this table to sum them up
),
PrevSell =
List.Sum(
Table.SelectRows(
t[[type], [Units], [Index]], each // 1. retrieve table "t" and only bring in columns: type, Units and Index
( [type] = "Sale" and [Index] < i ) // 2. filter down to type = Sale and Index < Index from the outer table (=cum. sum)
)[Units] // 3. Extract the units list from this table to sum them up
),
Cost of goods sold =
let pSell = if PrevSell = null or PrevSell - 1 < 0 then 0 else PrevSell in // make sure to get an initial 0 (= first position in the list)
if [type] = "Sale" then
List.Sum(
List.FirstN(
List.Skip( lCost, pSell ), // 1. Skip the number equal to pSell in the lCost list
Sell - pSell // 2. keep the number of items in the list that where sold and sum them up
)
) else null
]
So you could also write it like this, bringing in Units from the outer table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS5cgMxCIbfZWsmI0AXbV4hpcdFinRpU+Tto9NCa2OnWLH2fsP1Iy6XA5074KDyvP/8ljMn4GI8QwzF4nGFCWF5Pj6/vyrlwZGydMKGLz8PfuwmZQisrH+MCYF4ZUPHArUM4y0exwzc0uGeFm5gc9tJemNH0EvnrYTO8o2MAi4qjP+H+S0yrVo4MnipLw5I7opZoHcgomzUnF8cokCuAAlIqs20HBKEZfK5junOtT/qIWZm80C3IX4pgQFi/UklWvuGVjxa3u4kmEyon9uBd/2/taHWztLzMBIPwF5ZNHuP4IOyGPdBWiSJQCLVfdzar4RPBcCWIkidFMxG8AKEqCxaOnAGRGXJWeBNUrJkyAztIlEa5WxatCbMRr8hhXF1mkt+HdUbPckO3Jhx57tDC5xDQvGMjF2zCEuBvgDaGFF+opIMlfqlslWKoq174pGU7oy2xy0006tKma2dEMdeLgPs2ohYCqQwgAQBu17nSR8bs+xLz+0FqK30TQjRuvOoVIC5+bZXXCBl2VpKKcxp400PPZblFugBLg26Xv8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Symbol = _t, Units = _t, #"type" = _t, #"Total Value" = _t, #"value per unit" = _t, index = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Symbol", Int64.Type}, {"Units", Int64.Type}, {"Total Value", Currency.Type}, {"value per unit", Currency.Type}, {"index", Int64.Type}}),
GroupRows = Table.Group(ChType, {"Symbol"}, {{"AllRows", each
let
t = Table.AddIndexColumn( _, "Index", 0, 1 ), // consider a reinforcement of the sort order on this initial group by table
lCost = List.Combine( Table.AddColumn( Table.SelectRows( t[[type],[Units],[value per unit]], each ([type] = "Buy")), "Value", each List.Repeat( {[value per unit]}, [Units] ))[Value] ),
AddRecord = Table.AddColumn( t, "Values", each
[
i = [Index],
u = [Units],
Sell = List.Sum( Table.SelectRows( t[[type],[Units], [Index]], each ([type] = "Sale" and [Index] <= i))[Units] ),
PrevSell = List.Sum( Table.SelectRows( t[[type],[Units], [Index]], each ([type] = "Sale" and [Index] < i))[Units] ),
Cost of goods sold = let pSell = if PrevSell = null or PrevSell-1 < 0 then 0 else PrevSell in
if [type] = "Sale" then List.Sum( List.FirstN( List.Skip( lCost, pSell ), u )) else null
]
)
in
AddRecord, type table [Symbol=nullable number, Units=nullable number, type=nullable text, Total Value=nullable number, value per unit=nullable number, index=nullable number] }}
)[[AllRows]],
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"Symbol", "Units", "type", "Total Value", "value per unit", "Values"}, {"Symbol", "Units", "type", "Total Value", "value per unit", "Values"}),
GetCost = Table.ExpandRecordColumn(ExpandAllRows, "Values", {"Cost of goods sold"}, {"Cost of goods sold"})
in
GetCost
I hope this helps
Hi @Melissa
I have attached a sample .pbix file with the data from the actual model. How would the code be implemented here? The Costing table is drawing from a power bi service data flow model.
Best
Harvey
sample file.pbix (882.4 KB)
query txt.txt (1.9 KB)
The query text is traight from power bi service data flow
Hi @Harvey,
First ‘Dataflow’ excellent choice. I would recommend incorporating “Cost of goods sold” there as well.
Second there seems to be some inconsistancy in the provided sample… looks like you can have sales without a buy first OR without any buy (?) AND you can have neg buys due to corrections but posted on another Date ID
Here’s what I’ve done to work around that; sort on Type before Date ID and I’ve excluded those corrections on a FIFO basis, see this video for a similair approach, and if there are no buys it will return a null
.
Incorporated that in the dataflow script (since I can’t see the results I’m hoping it won’t give you any errors but I can’t test the code 
) BTW the Index you’d added previously was based on the Date ID, I removed that because you can still sort on Date ID - just put it back if you need that for something else…
let
Source = Table.Combine({#"Sales Query", #"GRV Query"}),
SortDateID = Table.Sort(Source, {{"Date ID", Order.Ascending}}),
RemoveErrors = Table.ReplaceErrorValues(SortDateID, {{"Units", 0}, {"Value per Unit", 0}}),
ReplaceNulls = Table.ReplaceValue(RemoveErrors, null, 0, Replacer.ReplaceValue, {"Units", "Value per Unit"}),
ReplaceNaN = Table.ReplaceValue(ReplaceNulls, #nan, 0, (value, old, new) => if Number.IsNaN(value) then new else value, {"Units", "Value per Unit"}),
ChColType = Table.TransformColumnTypes(ReplaceNaN, {{"Units", Int64.Type}, {"Value per Unit", type number}}),
AddTotalValue = Table.AddColumn(ChColType, "Total Value", each [Value per Unit] * [Units], type number),
GroupRows = Table.Group( AddTotalValue, {"Product ID"},
{
{"AllRows", each
let
t= Table.AddIndexColumn( Table.Sort( _,{{"Type", Order.Ascending}, {"Date ID", Order.Ascending}}), "Index", 0, 1 ),
BuysOnly = Table.SelectRows( t, each ([Type] = "Buy")),
AddAbsUnits = Table.AddColumn( BuysOnly, "Abs Units", each Number.Abs([Units]), Int64.Type),
AddAbsValue = Table.AddColumn( AddAbsUnits, "Abs Total", each Number.Abs([Total Value]), type number),
GroupBuys = Table.Group( AddAbsValue, {"Product ID", "Type", "Abs Units", "Abs Total"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Sum Units", each List.Sum([Units]), type nullable number},
{"AllRows2", each
let
buys = Table.Sort( _, {{"Units", Order.Ascending}, {"Index", Order.Ascending}}),
index2 = Table.AddIndexColumn( buys, "n", 1, 1, Int64.Type ),
running = Table.AddColumn( index2, "RT", each List.Sum( List.FirstN( index2[Units], [n] )), type number),
result = Table.SelectRows( running, each [RT] >0 )[[Units], [Value per Unit]]
in
result, type table [Units=nullable number, Value per Unit=nullable number]
}
}),
NoNulls = Table.SelectRows( GroupBuys, each ([Sum Units] <> 0))[[AllRows2]],
ExpandResult = Table.ExpandTableColumn( NoNulls, "AllRows2", {"Units", "Value per Unit"}, {"Units", "Value per Unit"}),
lCost = List.Buffer( List.Combine( Table.AddColumn( ExpandResult, "Value", each List.Repeat( {[Value per Unit]}, [Units] ))[Value] )),
AddRecord = Table.AddColumn( t, "Values", each
[
i = [Index],
u = [Units],
CumPrevSell = List.Sum( Table.SelectRows( t[[Type], [Units], [Index]], each ([Type] = "Sale" and [Index] < i))[Units] ),
Cost of goods sold = let pSell = if CumPrevSell = null or CumPrevSell-1 < 0 then 0 else CumPrevSell in
try
if [Type] = "Sale" then List.Sum( List.FirstN( List.Skip( lCost, pSell ), u )) else null
otherwise null
]
)
in
AddRecord, type table [Date ID=nullable number, Product ID=nullable number, Units=nullable number, Value per Unit=nullable number, Type=nullable text, Total Value=nullable number, Index=nullable number]
}
} )[[AllRows]],
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"Date ID", "Product ID", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}, {"Date ID", "Product ID", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}),
GetCost = Table.ExpandRecordColumn(ExpandAllRows, "Values", {"Cost of goods sold"}, {"Cost of goods sold"})
in
GetCost
I hope this is helpful
Hi @Melissa
This worked perfectly! No errors in the code. Thank you.
It is a massive data set and so I only filtered to get the last three months of data. Once the calculations are in place, like now, I will then take off the filters on the dataflow and that the data set will have those missing buys as you highlighted.
The negatives are transaction reversals and/or adjustments. They happen quite a lot. Some are vouchers that would have been redeemed from the system for example a customer can make a booking for a product and receive a voucher and upon collection it is then deducted from stock etc. Would you then say your fix dealt effectively with this issue?
I have implemented the code inside the dataflow, looks good. Just a note, in the database there are some cases where a sale appears before a buy, this is because the database in incomplete. We had to choose a cut off date as some of the data was garbage to be honest. How would you recommend to deal with such an issue? Only consider sale after the first buy for each product and skip all other transactions that would have occurred before an initial buy?
Best
Harvey
Hi @Harvey,
Glad to hear it worked well for you.
Ultimately it is up to you and your stakeholders to define rules that make to most sense for your company/business. Like I mentioned in my previous post, this is how its being handled now:
However you can always amend the code if that need arises.
All the best.