Dynamic column comparison of last column

[scott63070]

I have a table similar to this:

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

Each month a new column is added.  I would like to add columns in power query to show the difference and percent change between the current month and the previous month and one year prior month.  Numbers in this table are randomized.

Thanks for your assistance,

[MFelix · Accepted solution]

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.

[audreygerred · Accepted solution]

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

[v-pgoloju]

Hi @scott63070,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @scott63070,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @MFelix , @audreygerred and @ronrsnfld  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[ronrsnfld · Accepted solution]

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)

[audreygerred · Accepted solution]

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

[MFelix · Accepted solution]

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.