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.

     

     

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?

v-kkf-msft's avatar
v-kkf-msft
Community Support
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.