Forum Discussion

Power_It_Up's avatar
Power_It_Up
Helper II
2 years ago
Solved

Find % difference between two columns in matrix table

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.

  • 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!

     

4 Replies

  • Alex87's avatar
    Alex87
    Solution Sage

    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's avatar
      Power_It_Up
      Helper II

      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's avatar
    Alex87
    Solution Sage

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

    • Power_It_Up's avatar
      Power_It_Up
      Helper II

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