FIFO Cost Valuation for The sales transactions

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

2 Likes

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 :+1:

1 Like

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

1 Like

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 :hand_with_index_finger_and_thumb_crossed:) 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

1 Like

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:

  • Iā€™ve sorted on Type before Date ID ensuring to get a value if a sale is before a buy,
  • excluded corrections on a FIFO basis,
  • and if there are no buys at all a null is returned

However you can always amend the code if that need arises.
All the best.

1 Like