Calculate purchase price of last n transacrtions

[Daringg]

Hello,

I have this buy and sale table, i want to calculate the profit per each sale, by getting the buy prices until the sale volume is reached:

The profit at the last record should be:

40*3.75 - (20*25+10*3+10*2)

Any ideas how can I achieve this in Power Query?

Regards,
Darin

[v-veshwara-msft]

Hi @Daringg ,
Thanks for using Microsoft Fabric Community,

Thank you for the detailed explanation and images.

Also, thanks to @lbendlin  for the insightful guidance. As rightly pointed out, handling this kind of profit calculation requires atomizing the buys and applying FIFO logic to track the cost of goods sold.

Requirement Summary:

Profit needs to be calculated for each sale by using the earliest available buys first, following FIFO (First In First Out). If one Buy entry isn't enough to cover the Sell quantity, the next Buy is used, continuing until the total is fulfilled. The cost is calculated using the unit prices from those Buy rows.

Here's a version using Power Query (M)  (Replace "Source" with your table name):

let
// Load your source table
Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],

// Add an Index to maintain order
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

// Separate Buy and Sell rows
BuyRows = Table.SelectRows(AddIndex, each [Transaction] = "Buy"),
SellRows = Table.SelectRows(AddIndex, each [Transaction] = "Sell"),

// Expand Buy rows into one row per unit
ExpandedBuy = Table.ExpandListColumn(
Table.AddColumn(BuyRows, "UnitPriceList", each List.Repeat({[Item price]}, [Volume])),
"UnitPriceList"
),
FIFO = Table.SelectColumns(ExpandedBuy, {"Index", "Date", "Item", "UnitPriceList"}),
RenamedFIFO = Table.RenameColumns(FIFO, {{"UnitPriceList", "UnitPrice"}}),
InventoryList = Table.ToRecords(RenamedFIFO),

// Function to process each Sell row
ProcessSell = (sellRow as record, inventory as list) as record =>
let
volumeToSell = sellRow[Volume],
saleAmount = sellRow[Total amount],

// Get FIFO units
unitsUsed = List.FirstN(inventory, volumeToSell),
cost = List.Sum(List.Transform(unitsUsed, each _[UnitPrice])),
profit = saleAmount - cost,

// Group by Date and count units per date
groupedByDateTable = Table.Group(
Table.FromRecords(unitsUsed),
{"Date"},
{{"Volume", each Table.RowCount(_), Int64.Type}}
),
groupedList = Table.ToRecords(groupedByDateTable),
padded = groupedList & List.Repeat({[Date=null, Volume=null]}, 2 - List.Count(groupedList)),

// Remaining inventory
newInventory = List.Skip(inventory, volumeToSell),

// Add fields to the sell row
result1 = Record.AddField(sellRow, "Sold on date 1", padded{0}[Date]),
result2 = Record.AddField(result1, "Volume sold on date 1", padded{0}[Volume]),
result3 = Record.AddField(result2, "Sold on date 2", padded{1}[Date]),
result4 = Record.AddField(result3, "Volume sold on date 2", padded{1}[Volume]),
resultFinal = Record.AddField(result4, "Transaction revenue", profit)
in
[Inventory = newInventory, Result = resultFinal],

// Loop over Sell rows
FinalResult = List.Accumulate(
Table.ToRecords(SellRows),
[Inventory = InventoryList, Result = {}],
(state, sell) =>
let
processed = ProcessSell(sell, state[Inventory])
in
[
Inventory = processed[Inventory],
Result = state[Result] & {processed[Result]}
]
)[Result]
in
Table.FromRecords(FinalResult)

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.


Thank you.

[Daringg]

Hi @v-veshwara-msft ,

I've got it running, but the result seems to be wrong, perhaps I've messed it up. Here is my final code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\e0001805\My docs\MyGoogleDocs\DSK\My_Trades_testing.xlsx"), null, true),
    Trades_Sheet = Source{[Item="Trades",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Trades_Sheet,{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Item", type text}, {"Volume", Int64.Type}, {"Transaction", type text}, {"Total amount", type number}, {"Item price", type number}, {"Валута", type text}, {"Обща стойност в EUR", type number}, {"Паричен поток", type number}, {"Месец", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Date", "Item", "Volume", "Transaction", "Total amount", "Item price"}),
    // Add an Index to maintain order
    AddIndex = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1, Int64.Type),

    // Separate Buy and Sell rows
    BuyRows = Table.SelectRows(AddIndex, each [Transaction] = "Buy"),
    SellRows = Table.SelectRows(AddIndex, each [Transaction] = "Sell"),

    // Ensure Volume is positive for List.Repeat
    ExpandedBuy = Table.ExpandListColumn(
        Table.AddColumn(BuyRows, "UnitPriceList", each List.Repeat({[Item price]}, Number.Abs([Volume]))),
        "UnitPriceList"
    ),
    FIFO = Table.SelectColumns(ExpandedBuy, {"Index", "Date", "Item", "UnitPriceList"}),
    RenamedFIFO = Table.RenameColumns(FIFO, {{"UnitPriceList", "Item price"}}),
    InventoryList = Table.ToRecords(RenamedFIFO),

    // Function to process each Sell row
    ProcessSell = (sellRow as record, inventory as list) as record =>
    let
        volumeToSell = Number.Abs(sellRow[Volume]), // Ensure volumeToSell is positive
        saleAmount = sellRow[Total amount],

        // Get FIFO units
        unitsUsed = List.FirstN(inventory, volumeToSell),
        cost = List.Sum(List.Transform(unitsUsed, each _[Item price])),
        profit = saleAmount - cost,

        // Group by Date and count units per date
        groupedByDateTable = Table.Group(
            Table.FromRecords(unitsUsed),
            {"Date"},
            {{"Volume", each Table.RowCount(_), Int64.Type}}
        ),
        groupedList = Table.ToRecords(groupedByDateTable),
        padded = groupedList & List.Repeat({[Date=null, Volume=null]}, 2 - List.Count(groupedList)),

        // Remaining inventory
        newInventory = List.Skip(inventory, volumeToSell),

        // Add fields to the sell row
        result1 = Record.AddField(sellRow, "Sold on date 1", padded{0}[Date]),
        result2 = Record.AddField(result1, "Volume sold on date 1", padded{0}[Volume]),
        result3 = Record.AddField(result2, "Sold on date 2", padded{1}[Date]),
        result4 = Record.AddField(result3, "Volume sold on date 2", padded{1}[Volume]),
        resultFinal = Record.AddField(result4, "Transaction revenue", profit)
    in
        [Inventory = newInventory, Result = resultFinal],

    // Loop over Sell rows
    FinalResult = List.Accumulate(
        Table.ToRecords(SellRows),
        [Inventory = InventoryList, Result = {}],
        (state, sell) =>
        let
            processed = ProcessSell(sell, state[Inventory])
        in
            [
                Inventory = processed[Inventory],
                Result = state[Result] & {processed[Result]}
            ]
    )[Result]
in
    Table.FromRecords(FinalResult)

 Here is some real data of mine:

And here is the result of the query:

The calculated revenue of the first sell is wrong.

Can you have a look?

Thanks again!

Darin

[v-veshwara-msft]

Hi @Daringg ,

We wanted to follow up and check if the solutions provided addressed your needs. If so, kindly mark it as a solution to assist others. If you require further assistance, please don't hesitate to reach out.

Thank you.

[Daringg]

I do apologize for my delay, but currently I cant test it, i will revert back as soon as i can.

Thank ou very much for your support!

Kind regards,
Darin

[v-veshwara-msft]

Hi @Daringg ,

Just wanted to check in to see if you had a chance to test the solution provided. If you did, please share the feedback here to assist you further. If the provided response has addressed your query, please consider marking it as "Accepted Solution" to help the community find the answer quickly.

Thank you.

[Daringg]

Hi @v-veshwara-msft ,

Sory but got stuck at one step and can't find time to deal with it:

while to the previous there is no error:

I will appretiate if you can assist e with this.

Thank you!

[v-veshwara-msft · Accepted solution]

Hi @Daringg ,
Thanks for the follow-up and the screenshots.
In your screenshot, the last line says in ProcessSell, which returns the function definition itself rather than executing it.
In contrast, the second image runs fine because it's a simpler version that ends cleanly with a return value.
To fix this, make sure the ProcessSell function is used as part of your logic, and the query ends by returning the actual transformed result, like a record or a table.

For example:

in
    Table.FromRecords(FinalResult)

Below is a corrected version of the ProcessSell function you can plug into your code:

ProcessSell = (sellRow as record, inventory as list) as record =>
    let
        volumeToSell = sellRow[Volume],
        saleAmount = sellRow[Total amount],
        unitsUsed = List.FirstN(inventory, volumeToSell),
        cost = List.Sum(List.Transform(unitsUsed, each _[UnitPrice])),
        profit = saleAmount - cost,
        groupedByDate = List.Buffer(Table.Group(Table.FromRecords(unitsUsed), {"Date"}, {
            {"Volume", each Table.RowCount(_), Int64.Type}
        })),
        padList = groupedByDate & List.Repeat({[Date=null, Volume=null]}, 2 - List.Count(groupedByDate)),
        newInventory = List.Skip(inventory, volumeToSell),
        r1 = Record.AddField(sellRow, "Sold on date 1", padList{0}[Date]),
        r2 = Record.AddField(r1, "Volume sold on date 1", padList{0}[Volume]),
        r3 = Record.AddField(r2, "Sold on date 2", padList{1}[Date]),
        r4 = Record.AddField(r3, "Volume sold on date 2", padList{1}[Volume]),
        rFinal = Record.AddField(r4, "Transaction revenue", profit)
    in
        [Inventory = newInventory, Result = rFinal]

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.

[v-veshwara-msft]

Hi @Daringg ,
Just checking in again to see if the above response has been helpful. If yes, would you mind marking it as "Accepted Solution" to benefit others. If further assistance is needed, please reach out.
Thank you.

[Daringg]

Thanks for your prompt reply lbendlin!

But it is not a loss, because the sell is for 40 pieces only, while the purchase is for 70. Which leads me to identifying a problem in my question/example:

the purchase price of the sold intems should be calculated "First In First Out", so in my example:

we are slling 40*3.75 for which as a purchase price we should look for the firs 40 that are bought, and those are 40*2. And so for this transaction the profit should be 150 - 80...

But how do we calculate the next sale if it is for 15 pieces: it then should identify that the first purchase has been sold so the next 15 should cost 10 * 3.00 and 5 * 2.50...

[lbendlin]

You have two buys on the same day. You will need to provide an index so that these items can be correctly attributed chronologically.

Now what should happen if you have another Sell for 30 units?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

I would approach this by atomizing all buys , each with their buy price, and then using a FIFO buffer to calculate the value that is consumed by each sale.

[Daringg]

Sorry for my late answer, and I realy apretiate your help!

Here is a deeper example:

and here are the tree formulas of cells K6, K7 & K8:

I just can figure how can this be acomplished 😞

Regards,

Darin

[lbendlin]

You already have the total amount. You should be able to use 150 - (80+30+50)  which would result in a loss of 10.

Do you need to worry about exchange rates?