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.

Account	Date	Value
A	8/1/2024	6.9
A	9/1/2024	3.2
A	10/1/2024	9.4
A	11/1/2024	4.1
A	12/1/2024	9.5
A	1/1/2025	2.1
A	2/1/2025	6.9
A	3/1/2025	2.8
A	4/1/2025	4.2
A	5/1/2025	0.5
A	6/1/2025	5.2
A	7/1/2025	3.2
A	8/1/2025	3.7
A	9/1/2025	4.5
A	10/1/2025	2.4
B	8/1/2024	1.3
B	9/1/2024	0.5
B	10/1/2024	3
B	11/1/2024	9
B	12/1/2024	6.1
B	1/1/2025	4
B	2/1/2025	1.3
B	3/1/2025	5.3
B	4/1/2025	8.2
B	5/1/2025	2.9
B	6/1/2025	5.4
B	7/1/2025	0.5
B	8/1/2025	4.6
B	9/1/2025	6
B	10/1/2025	2.3
C	8/1/2024	2.3
C	9/1/2024	3.3
C	10/1/2024	1.5
C	11/1/2024	8.4
C	12/1/2024	3.8
C	1/1/2025	4.8
C	2/1/2025	2.3
C	3/1/2025	3.8
C	4/1/2025	9.2
C	5/1/2025	7.2
C	6/1/2025	3.9
C	7/1/2025	3.3
C	8/1/2025	7.4
C	9/1/2025	5.9
C	10/1/2025	8.3
D	8/1/2024	0.3
D	9/1/2024	0.9
D	10/1/2024	1.3
D	11/1/2024	1.4
D	12/1/2024	0.2
D	1/1/2025	0.1
D	2/1/2025	0.3
D	3/1/2025	4.7
D	4/1/2025	0.1
D	5/1/2025	2.6
D	6/1/2025	2.1
D	7/1/2025	0.9
D	8/1/2025	8.9
D	9/1/2025	5.5
D	10/1/2025	7.9
E	8/1/2024	3.9
E	9/1/2024	5.3
E	10/1/2024	5.3
E	11/1/2024	4.1
E	12/1/2024	0.3
E	1/1/2025	8.8
E	2/1/2025	3.9
E	3/1/2025	8.1
E	4/1/2025	5.2
E	5/1/2025	0.9
E	6/1/2025	3.5
E	7/1/2025	5.3
E	8/1/2025	3.6
E	9/1/2025	8.8
E	10/1/2025	3.9

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)