Forum Discussion
Matrix – percent change between columns
CleliaComes This measure should give you the percent difference between your months:
Percent Difference =
VAR thisMonth =
SUM ( 'GL PL'[Saldi resultaat] )
VAR lastMonth =
CALCULATE ( SUM ( 'GL PL'[Saldi resultaat] ), PREVIOUSMONTH ( Kalender[Date] ) )
RETURN
IF (
OR ( ISBLANK ( thisMonth ), ISBLANK ( lastMonth ) ),
BLANK (),
DIVIDE ( thisMonth - lastMonth, lastMonth )
)CleliaComes I think there are two parts here!
1. Showing the percentage including a previous month that may be filtered out (such as if you have December 2020 and only showing 2021 -- we want to show that January 2021 percent difference from December 2020). That can be resolved with this modification:
Percent Difference =
VAR thisMonth =
SUM ( 'GL PL'[Saldi resultaat] )
VAR lastMonth =
CALCULATE (
SUM ( 'GL PL'[Saldi resultaat] ),
ALL ( Kalender ),
PREVIOUSMONTH ( Kalender[Date] )
)
RETURN
IF (
OR ( ISBLANK ( thisMonth ), ISBLANK ( lastMonth ) ),
BLANK (),
DIVIDE ( thisMonth - lastMonth, lastMonth )
)
2. The second is adjusting the matrix formatting a little. To have it take up less space I would first try simply renaming the "Perecent Difference" to "%" or something small so it doesn't take up so much space.
The second approach is to create a new measure that joins the value in a more meaningful way into a single column, such as this:
Matrix Values =
-- If the value is blank, return blank, otherwise return it formatted as a whole number
IF (
ISBLANK ( SUM ( 'GL PL'[Saldi resultaat] ) ),
BLANK (),
FORMAT ( SUM ( 'GL PL'[Saldi resultaat] ), "#,##0" )
) -- If the percenage difference is blank, return blank, otherwise return the percentage appended
-- to the number in the right format as well as a +/- sign in ()s.
& IF (
ISBLANK ( [Percent Difference] ),
BLANK (),
" ("
& IF ( [Percent Difference] > 0, "+", "" )
& FORMAT ( [Percent Difference], "Percent" ) & ")"
)
Hi DataZoe
It works perfectly! Thanks a lot also to adjust the calendar table! 😀
The only thing is that the first % diff column is empty in the example because there is no data for December 2020. Or when I add 2020, January 2021 % whill shown the diff between December 2020- January 2021. I tried to add a year filter on the visual but that doesn't change anything. I was looking at the forum how to hide a column in a matrix but that seems not so easy and the solution I found deleted also the result column of January. Do you have any idea?
kr,
Clélia
CleliaComes I think there are two parts here!
1. Showing the percentage including a previous month that may be filtered out (such as if you have December 2020 and only showing 2021 -- we want to show that January 2021 percent difference from December 2020). That can be resolved with this modification:
VAR thisMonth =
SUM ( 'GL PL'[Saldi resultaat] )
VAR lastMonth =
CALCULATE (
SUM ( 'GL PL'[Saldi resultaat] ),
ALL ( Kalender ),
PREVIOUSMONTH ( Kalender[Date] )
)
RETURN
IF (
OR ( ISBLANK ( thisMonth ), ISBLANK ( lastMonth ) ),
BLANK (),
DIVIDE ( thisMonth - lastMonth, lastMonth )
)
The second approach is to create a new measure that joins the value in a more meaningful way into a single column, such as this:
-- If the value is blank, return blank, otherwise return it formatted as a whole number
IF (
ISBLANK ( SUM ( 'GL PL'[Saldi resultaat] ) ),
BLANK (),
FORMAT ( SUM ( 'GL PL'[Saldi resultaat] ), "#,##0" )
) -- If the percenage difference is blank, return blank, otherwise return the percentage appended
-- to the number in the right format as well as a +/- sign in ()s.
& IF (
ISBLANK ( [Percent Difference] ),
BLANK (),
" ("
& IF ( [Percent Difference] > 0, "+", "" )
& FORMAT ( [Percent Difference], "Percent" ) & ")"
)
Thanks a lot for helping me with this matter and all the other extra information! 🙂 It is exactly what I was looking for.
Regards,Clélia