Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need Date Diff % in the Matrix table

 Hi Everyone,

I have Matrix table like below

Month  2021 2020

1           10     20

2            30    40

3            50    60

 

Now i need to calculate Diff%Column in Matrix.I couldn't place measure in Columns section of Matrix table.


Output Should be:

 

Month  2021 2020 DateDiff%

1           10     20     50%

2            30    40     30%

3            50    60     30%


Thanks in Advance

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    In this case, I think it is easier to display DateDiff% in the column total.

    Modify the name of the Column subtotals to "DateDiff%".

     

     

    And then create the following measure:

     

     

    Measure = 
var Max_Year = MAXX( ALLSELECTED('Table'), 'Table'[Year] ) 
var Min_Year = MINX( ALLSELECTED('Table'), 'Table'[Year] ) 
return
    IF(
        HASONEVALUE('Table'[Year]),
        SUM('Table'[Value]),
        FORMAT( 1 - DIVIDE(
                        CALCULATE( SUM('Table'[Value]), 'Table'[Year] = Max_Year ),
                        CALCULATE( SUM('Table'[Value]), 'Table'[Year] = Min_Year )
                    ),
                "0.00%"
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You need to have three measures. Year can not be a column and then you get diff

     

     

    example 

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    or

     

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series

     

     

     

    Hybrid table is one possibility

    if you are looking for a Hybrid display with Matrix Column and measure
    https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
    https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

    vote for Hybrid Table
    https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak 

       

      Thanks for your reply.

      Requirement for me is to have the Year as column name and to find the diff% for every month.

      We don't have any other option to get like that?

      thanks.

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Create a new table:

         

         

        Then try the following measure:

         

        Measure = 
SWITCH(
    MAX('Matrix Columns'[Column]),
    "2020", SUM('Table'[Value]),
    "2021", SUM('Table'[Value]),
    "DateDiff%", FORMAT(
                    1 - DIVIDE(
                        CALCULATE(SUM('Table'[Value]), 'Matrix Columns'[Column] = "2021"),
                        CALCULATE(SUM('Table'[Value]), 'Matrix Columns'[Column] = "2020")
                        ),
                    "0.00%"
                )
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.