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

     

     

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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi v-kkf-msft 

         

        Thanks for your reply.

        Can this measure be dynamic,it means if i move to 2022 i need to have 2021 and 2022 in my table as column names?