Matrix – percent change between columns

[CleliaComes]

Hi

Is there a DAX formula to calculate and display the % change in a matrix between the months?
To make it more clear, I added my file, pls click here.

Kind regards,

Clélia

[DataZoe · Accepted solution]

@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]

Hi @DataZoe 

Can I ask you one more question: what if I add new data for a new year for example for '2020' in the same matrix with a slicer? I tried it and then I guess I have to add something to the formula? The year 2020 works - 2021 doens't give any result.

The updated file can be found here 

Kr

Clélia

[DataZoe]

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

[CleliaComes]

Thanks a lot for your help! I will figure it out tomorrow! Regards, Clélia

[CleliaComes]

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

[DataZoe · Accepted solution]

@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]

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

[CleliaComes]

Thanks a lot for your quick & clear answer! 
Kr,

Clélia

[DataZoe]

@CleliaComes You're welcome! Thank you for sharing your file as well!

[DataZoe · Accepted solution]

@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 )
)