Forum Discussion
Find % difference between two columns in matrix table
- 2 years ago
1. Make sure your format is Date for the column "Month"
2. Add a new column MonthNb
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTJR0lQwMDpVgdiKARTNASIWYMV2gEFIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Value", Int64.Type}}), #"Inserted Month" = Table.AddColumn(#"Changed Type", "MonthNb", each Date.Month([Month]), Int64.Type) in #"Inserted Month"3. Close Power Query and change the format of the column Month
4. Add the DAX measures:
Diff MoM = VAR _CurrentValue = SUM(YourTableName[Value]) VAR _vTable = OFFSET( -1, ALL( YourTableName[Month], YourTableName[Value], YourTableName[MonthNb] ), ORDERBY(YourTableName[Month], ASC), ) VAR _Selection = SELECTCOLUMNS( _vTable, YourTableName[Value]) VAR _PreviousValue = SUMX(_Selection, YourTableName[Value]) VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue) RETURN _Difference ============================ %Diff MoM = VAR _CurrentValue = SUM(YourTableName[Value]) VAR _vTable = OFFSET( -1, ALL( YourTableName[Month], YourTableName[Value], YourTableName[MonthNb] ), ORDERBY(YourTableName[Month], ASC), ) VAR _Selection = SELECTCOLUMNS( _vTable, YourTableName[Value]) VAR _PreviousValue = SUMX(_Selection, YourTableName[Value]) VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue) RETURN DIVIDE(_Difference, _PreviousValue) ================== SumValue = SUM('YourTableName'[Value])5. Use a Matrix column with the column Month on Columns and SumValues, Diff MoM and %Diff MoM
In the format pane choose Values/ Options/ Switch values to rows ON
Result
If it answers your query, please mark my reply as the solution. Thanks!
1. Make sure your format is Date for the column "Month"
2. Add a new column MonthNb
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTJR0lQwMDpVgdiKARTNASIWYMV2gEFIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Value", Int64.Type}}),
#"Inserted Month" = Table.AddColumn(#"Changed Type", "MonthNb", each Date.Month([Month]), Int64.Type)
in
#"Inserted Month"
3. Close Power Query and change the format of the column Month
4. Add the DAX measures:
Diff MoM =
VAR _CurrentValue = SUM(YourTableName[Value])
VAR _vTable =
OFFSET(
-1,
ALL(
YourTableName[Month],
YourTableName[Value],
YourTableName[MonthNb]
),
ORDERBY(YourTableName[Month], ASC),
)
VAR _Selection =
SELECTCOLUMNS( _vTable, YourTableName[Value])
VAR _PreviousValue = SUMX(_Selection, YourTableName[Value])
VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue)
RETURN
_Difference
============================
%Diff MoM =
VAR _CurrentValue = SUM(YourTableName[Value])
VAR _vTable =
OFFSET(
-1,
ALL(
YourTableName[Month],
YourTableName[Value],
YourTableName[MonthNb]
),
ORDERBY(YourTableName[Month], ASC),
)
VAR _Selection =
SELECTCOLUMNS( _vTable, YourTableName[Value])
VAR _PreviousValue = SUMX(_Selection, YourTableName[Value])
VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue)
RETURN
DIVIDE(_Difference, _PreviousValue)
==================
SumValue = SUM('YourTableName'[Value])
5. Use a Matrix column with the column Month on Columns and SumValues, Diff MoM and %Diff MoM
In the format pane choose Values/ Options/ Switch values to rows ON
Result
If it answers your query, please mark my reply as the solution. Thanks!
Hi Alex87. Firstly, my apologies for not replying to you sooner. Many thanks for your input on this. Apart from the percentages being slightly out (should be -10% and +33%, which I amended), this worked well and it was insightful how you used the OFFSET function. Thanks again. 🙂