Power Query previous row value based on criteria!! :O

If you are looking for a Power Query solution, I have 2 alternatives.

In both cases I created an Excel workbook with your data; the step "Typed" is the actual starting point.

Alternative 1 is based on this blog by MattAllington using indices and merge tables.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table  = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Typed, "Index1", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index0"},#"Added Index1",{"Index1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer", "Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index1", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    #"Removed Other Columns"

Alternative 2 is an alternative from myself.
A bit more complicated but I just like to share it as I think it might be interesting.

I created a function PreviousRecords that adds a null record as first row to a table, removes the last row of that table and turns the result into a list of records.

In the query I turn the original table into a list of records and create a table from this list and the list from the function.

Function PreviousRecords:

(SourceTable as table) as list =>
let
    Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}),
    Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_),
    Records = Table.ToRecords(Renamed)
in
    Records

Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    Records = Table.FromColumns({Table.ToRecords(Typed),PreviousRecords(Typed)}),
    ExpandedCurrent = Table.ExpandRecordColumn(Records, "Column1", {"Customer", "Month", "Current Month Sales"}, {"Customer", "Month", "Current Month Sales"}),
    ExpandedPrevious = Table.ExpandRecordColumn(ExpandedCurrent, "Column2", {"Previous.Customer", "Previous.Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    AddedConditionalColumn = Table.AddColumn(ExpandedPrevious, "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn,{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    RemovedOtherColumns