Find % difference between two columns in matrix table

[Power_It_Up]

Hi all,

Below Matrix table shows Month headers (text format) and some values for each.

I am wanting to create two row calcs to show the difference between two months and the % difference:-

In Tableau, I used a LOOKUP/OFFSET function to achieve this.

I've tried using many functions like PARALLELPERIOD or PREVIOUSMONTH, but they all error as the columns are not DATE driven, but TEXT.

Can someone help me on this?

Many thanks.

[Alex87 · Accepted solution]

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!

[Power_It_Up]

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. 🙂

[Alex87]

is there any reason you don't want to use DATE format?

[Power_It_Up]

@Alex87 Hi.  Not really.  I used the FORMAT option to change the full date to just months, which made them into TEXT.