Create a new column with calculation using conditions

[datanalysis]

Hi I have input data that looks like this, and I'd like to create a view with a new column "Month on Month Change", calculating difference on col "Actual" between current month and last month for items fall under the same "Segment". 

Input:

Segment	Section	Month	Year	Expected	Actual	Expected VS Actual	Owner
Transportation	General	1+11	2023	102	99	3	David
Transportation	General	2+10	2023	99	100	-1	David
Transportation	General	3+9	2023	91	91	0	David
Transportation	General	4+8	2023	100	99	1	David
Accommodation	General	1+11	2023	66	70	-4	John
Accommodation	General	2+10	2023	67	68	1	John
Accommodation	General	3+9	2023	67	67	0	John
Accommodation	General	4+8	2023	67	66	1	John

i.e: 

Month on Month change Row 1 = blank as there is no previous month data available for this segment

Month on Month change Row 2 = 1 (calculation being: select 'Segment - Transportation',  use 'Actual 2+10' - 'Actual 1+11')

Segment	Section	Month	Year	Expected	Actual	Expected VS Actual	Owner	Month on month change
Transportation	General	1+11	2023	102	99	3	David
Transportation	General	2+10	2023	99	100	-1	David	1

Following this logic, the full Output will look like:

Segment	Section	Month	Year	Expected	Actual	Expected VS Actual	Owner	Month on month change
Transportation	General	1+11	2023	102	99	3	David
Transportation	General	2+10	2023	99	100	-1	David	1
Transportation	General	3+9	2023	91	91	0	David	-9
Transportation	General	4+8	2023	100	99	1	David	8
Accommodation	General	1+11	2023	66	70	-4	John
Accommodation	General	2+10	2023	67	68	1	John	-2
Accommodation	General	3+9	2023	67	67	0	John	-1
Accommodation	General	4+8	2023	67	66	1	John	-1

Is there a way to acheive this in PowerQuery?

[AlienSx · Accepted solution]

    s = your_table,
    f = (tbl) => 
        [rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "Month"))), 
        gen = List.Generate(
            () => [i = 0, r = rows{0} & [Month on month change = 0]],
            (x) => x[i] < List.Count(rows),
            (x) => [i = x[i] + 1, r = rows{i} & [Month on month change = rows{i}[Actual] - x[r][Actual]]],
            (x) => x[r]
        )][gen],
    group = Table.Group(s, "Segment", {"x", f}),
    z = Table.FromRecords(List.Combine(group[x]))

[dufoq3 · Accepted solution]

Hi @datanalysis, different approach here.

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCsuyC8qSSzJzM9T0lFyT81LLUrMAbIMtQ0NgZSRgZExiGdgBCQtLYEEiOuSWJaZohSrg9cEI21DA4QJYL2GBiARXUNijTDWtkQywRBGGBCr30TbAtkPBnB3oBjgmJycn5ubn0IoEMzMgIQ52AcmQMIrPyOPgH7UIDAzBxEWUPuJ0I7ifYhuc6j3idCN4nmIbjMUu2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Section = _t, Month = _t, Year = _t, Expected = _t, Actual = _t, #"Expected VS Actual" = _t, Owner = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Expected", type number}, {"Actual", type number}, {"Expected VS Actual", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Segment"}, {{"All", each 
        [ a = fnShift(_,"Actual",1, null, type number),
          b = Table.AddColumn(a, "Month on month change", (x)=> x[Actual] - x[Actual_PrevValue], type number),
          c = Table.RemoveColumns(b, {"Actual_PrevValue"})
        ][c], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[AlienSx · Accepted solution]

    s = your_table,
    f = (tbl) => 
        [rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "Month"))), 
        gen = List.Generate(
            () => [i = 0, r = rows{0} & [Month on month change = 0]],
            (x) => x[i] < List.Count(rows),
            (x) => [i = x[i] + 1, r = rows{i} & [Month on month change = rows{i}[Actual] - x[r][Actual]]],
            (x) => x[r]
        )][gen],
    group = Table.Group(s, "Segment", {"x", f}),
    z = Table.FromRecords(List.Combine(group[x]))