DAX - Difference between Last 2 columns

Question

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 the LAST 2 columns, this is to calculate an increase in Sales for last month. Any idea of how this can be done in PowerBI using DAX or Power Query? Thanks

Answer 1

#"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.