Power Query Formula

Hi deblacus ,

Thanks for all the replies!

And deblacus , I have expanded the sample data according to your needs. Please check whether this is the result you want:

let
    Source = Table.FromRows({
        {4000, "Hats", "Commercial Sales", 600, "Actual", "10/31/2024"},
        {4000, "Hats", "Commercial Sales", 450, "Actual", "9/30/2024"},
        {4000, "Hats", "Commercial Sales", 200, "Actual", "8/31/2024"},
        {4001, "Scarves", "Commercial Sales", 300, "Actual", "10/31/2024"},
        {4001, "Scarves", "Commercial Sales", 400, "Actual", "9/30/2024"},
        {4001, "Scarves", "Commercial Sales", 500, "Actual", "8/31/2024"},
        {4002, "Gloves", "Commercial Sales", 150, "Actual", "10/31/2024"},
        {4002, "Gloves", "Commercial Sales", 100, "Actual", "9/30/2024"},
        {4002, "Gloves", "Commercial Sales", 75, "Actual", "8/31/2024"}
    }, {"NL Code", "Nominal Name", "Group", "Amount", "Reporting Type", "Month"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Month", type date}}),
    SortedTable = Table.Sort(ChangedType, {{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    AddMonthMovement = Table.AddColumn(AddedIndex, "Month Movement", each let
            CurrentIndex = [Index],
            PreviousRow = try AddedIndex{CurrentIndex - 2} otherwise null,
            PreviousAmount = if PreviousRow <> null and [NL Code] = PreviousRow[NL Code] then PreviousRow[Amount] else null
        in
            if PreviousAmount <> null then PreviousAmount - [Amount] else null),
    RemovedIndex = Table.RemoveColumns(AddMonthMovement, {"Index"})
in
    RemovedIndex

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Copy the following code and past it into Advance Editor to see the result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFDSUfJILCkGUs75ubmpRcmZiTkKwYk5qSAhM7C8Y3JJaWIOkGFsqG9ooG9kYGSkFKtDhHYTU1TtBvoGliRoN8Kw3cACqj0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"NL Code" = _t, #"Nominal Name" = _t, Group = _t, Amount = _t, #"Reporting Type" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NL Code", Int64.Type}, {"Nominal Name", type text}, {"Group", type text}, {"Amount", Int64.Type}, {"Reporting Type", type text}, {"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try [Amount]-Table.Max(Table.SelectRows(#"Changed Type", (x)=> x[Month]<[Month]),"Month")[Amount] otherwise "")
in
    #"Added Custom"