DAX - Difference between Last 2 columns

3
votes

Power BI Hi, I have a PowerBI report which has 1 Static column Object1 and Value's as Dynamic column. I want to add a Calculated Column which calculates the difference between LAST 2 columns, this is to calculate increase in the Sales for last month. Any idea how this can be done in PowerBI using DAX or Power Query? Thanks

1
sqlsql-serverpowerbidax
Are you expecting to keep changing the number or names of Value columns? – Alexis Olson
hey, the name of value columns will change. – ace_mccloud

1 Answers

1
votes

This is a bit clunky, but I think it does what you want.

#"Unpivoted Columns" = Table.UnpivotOtherColumns(PreviousStepNameHere, {"Object1"}, "Attribute", "Value"),
#"Filtered Last 2" = Table.SelectRows(#"Unpivoted Columns", each List.Contains(List.LastN(#"Unpivoted Columns"[Attribute], 2), [Attribute])),
#"Added Custom" = Table.AddColumn(#"Filtered Last 2", "Custom", each if List.Contains(List.LastN(#"Unpivoted Columns"[Attribute], 1), [Attribute]) then [Value] else -[Value]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Object1"}, {{"Value", each List.Sum([Custom]), type number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Attribute", each "Calculated_Column_Difference_Last2_Columns"),
#"Appended Query" = Table.Combine({#"Unpivoted Columns", #"Added Custom1"}),
#"Pivoted Column" = Table.Pivot(#"Appended Query", List.Distinct(#"Appended Query"[Attribute]), "Attribute", "Value")

Unpivot should preserve the column order. You filter the last two and switch the sign of the 2nd to last to get the difference when you group and sum. Add the desired column name as a custom column named Attribute. Append that back to your original unpivoted table and then re-pivot.