Dynamic column comparison of last column

Hi scott63070 ,

I suggest that you unpivot the column and then do a sort by Account and date and then using Index column do the difference and the percentage in new columns:

See the full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBJDsQwCAT/4vMIeSOxj7O9Isr/vzFOgZhcEDLdRZvjSM/0SJvMVZvUVaf0VbsUel210pumymB6KTNTpW9ed6bm6ul8HOm1+iIt9E0y5AyzoM+hUeqAVtmo5DFvlw1XZtrgv72/yA2OQui8WNpx0wy2X/zdM8/w7riUl+H8D9st/4ycxVPZHUpoOhewl0ra6lNj2o+UXRP+NzLo7QL9xhxkbk4ocfPsyTVcjY1//Xn+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"8/1/2024" = _t, #"9/1/2024" = _t, #"10/1/2024" = _t, #"11/1/2024" = _t, #"12/1/2024" = _t, #"1/1/2025" = _t, #"2/1/2025" = _t, #"3/1/2025" = _t, #"4/1/2025" = _t, #"5/1/2025" = _t, #"6/1/2025" = _t, #"7/1/2025" = _t, #"8/1/2025" = _t, #"9/1/2025" = _t, #"10/1/2025" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account"}, "Attribute", "Value"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", Currency.Type}}, "en-US"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Attribute", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Difference", each if  [Index] = 0 then null else 
if #"Changed Type"{[Index]-1}[Account] = [Account] then try #"Changed Type"{[Index]-1}[Value] - [Value] otherwise null else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "percentage", each if  [Index] = 0 then null else  if #"Changed Type"{[Index]-1}[Account] = [Account] then try [Difference] / #"Changed Type"{[Index]-1}[Value] otherwise null else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Difference", Currency.Type}, {"percentage", Percentage.Type}})
in
    #"Changed Type1"

But be aware that I believe that a best option is to use a DAX calculation instead of adding this values in the semantic model.

For this you just need to unpivot your data and then use a similar calculation to this:

Vs Previous month = SUM(Table[Value]) - CALCULATE(SUM(Table[Value]), DATEADD(Calendar[Date], -1 , Month))

% Value = DIVIDE( SUM(Table[Value])- [Vs Previous month], Vs Previous month)

This is more efficient and you don't need to add additonal data.

Hello! Rather than adding a new column every month, the data should be long and skinny (i.e. lots of rows and not a lot of columns). Each month new rows will be added. In Power BI you will have this table, a date table with a relationship to the fact table, and then you will use DAX Time Intelligence.

It is not difficult to do this with your existing format:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBJDsQwCAT/4vMIeSOxj7O9Isr/vzFOgZhcEDLdRZvjSM/0SJvMVZvUVaf0VbsUel210pumymB6KTNTpW9ed6bm6ul8HOm1+iIt9E0y5AyzoM+hUeqAVtmo5DFvlw1XZtrgv72/yA2OQui8WNpx0wy2X/zdM8/w7riUl+H8D9st/4ycxVPZHUpoOhewl0ra6lNj2o+UXRP+NzLo7QL9xhxkbk4ocfPsyTVcjY1//Xn+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"8/1/2024" = _t, #"9/1/2024" = _t, #"10/1/2024" = _t, #"11/1/2024" = _t, #"12/1/2024" = _t, #"1/1/2025" = _t, #"2/1/2025" = _t, #"3/1/2025" = _t, #"4/1/2025" = _t, #"5/1/2025" = _t, #"6/1/2025" = _t, #"7/1/2025" = _t, #"8/1/2025" = _t, #"9/1/2025" = _t, #"10/1/2025" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Account", type text}} & List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, type number})),
    
    #"Add Comparisons" = Table.AddColumn(#"Changed Type","Comparisons", (r)=>
        [a=Record.FieldValues(r),
         b=List.LastN(a,2),
         c=List.LastN(a,13),
         PrevMonth=(b{1}-b{0})/b{0},
         PrevYear=(c{12}-c{0})/c{0}]),
    
    #"Expanded Comparisons" = Table.ExpandRecordColumn(#"Add Comparisons", "Comparisons", {"PrevMonth", "PrevYear"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Comparisons",{
        {"PrevMonth", Percentage.Type}, {"PrevYear", Percentage.Type}})
in
    #"Changed Type1"

From your data (just showing the right half of the table)