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" ) & ")"
)
CleliaComes I did notice if your file the Kalendar table is not marked as a date table, so they may impact. The Year should be coming from the Kalendar table too in the above slicer.
When I did mark it as a date table, it converted the Date to a date format (removing year and month options), but you can correct for this by adding some columns like so:
Kalender =
ADDCOLUMNS (
CALENDARAUTO (),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
"Year", DATE ( YEAR ( [Date] ), 1, 1 ),
"Week",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"Monthly Week Number",
WEEKNUM ( [Date], 1 )
- WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 1 ) + 1,
"Yearly Week Number", WEEKNUM ( [Date] )
)
Then you can format your new Year and Month columns. I've included the changes in the attached PBIX.
Thanks a lot for your help! I will figure it out tomorrow! Regards, Clélia
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:
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" ) & ")"
)
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