How to fix my percentage increase/decrease for month on month returns?

[derekli17001]

 

  

 This is my current matrix where it shows the sales (cards) from 2020- 2024. The YOY percentages seem fine but when you drill down by month, the percentages month by month compare the difference from previous year rather than reflect the difference from last month. Eg) as seen in pic 2 , December 2021 says 11.95% because thats the improvement from December 2020. However, i want the percentage next to December 2021 to reflect the increase/decrease from November 2021.

These are my current measures in place: (note a date table is already included)

C% =
VAR CurrentYearCards =
    SUM ( 'Table'[Cards] )
VAR PreviousCards =
    CALCULATE ( SUM ( 'Table'[Cards] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    DIVIDE ( CurrentYearCards - PreviousYearCards, PreviousYearCards, 0 )

Min Year = CALCULATE(sum('Table'[Cards]),filter(ALL('Date'),'Date'[Year]=Minx(allselected('Date'),'Date'[Year])))

Max Year = CALCULATE(sum('Table'[Cards]),filter(ALL('Date'),'Date'[Year]=Maxx(allselected('Date'),'Date'[Year])))

Final % = if(isinscope('Date'[Year]), [C%], divide([Max Year]-[Min Year ],[Min Year ]))

[Jihwan_Kim · Accepted solution]

Hi,

I am not 100% sure if I understood your question correctly, but please try something like below whether it suits your requirement.

C% =
VAR CurrentYearCards =
    SUM ( 'Table'[Cards] )
VAR PreviousYearCards =
    CALCULATE ( SUM ( 'Table'[Cards] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
VAR PreviousMonthCards =
    CALCULATE ( SUM ( 'Table'[Cards] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Month] ), DIVIDE ( CurrentYearCards - PreviousMonthCards, PreviousMonthCards, 0 ),
        ISINSCOPE ( 'Date'[Year] ), DIVIDE ( CurrentYearCards - PreviousYearCards, PreviousYearCards, 0 )
    )

[derekli17001]

That was perfect, thanks. Your measure makes the bottom slot (highlighted in pic) blank - so i was wondering if theres a way to have a figure there that calculates the difference from 2024 to 2020? Thanks!

[Jihwan_Kim]

Hi,

I do not know how your semantic model looks like, but please try something like below.

C% =
VAR CurrentYearCards =
    SUM ( 'Table'[Cards] )
VAR PreviousYearCards =
    CALCULATE ( SUM ( 'Table'[Cards] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
VAR PreviousMonthCards =
    CALCULATE ( SUM ( 'Table'[Cards] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR _startingyear =
    MINX ( SUMMARIZE ( 'Table', 'Date'[Year] ), 'Date'[Year] )
VAR _latestyear =
    MAXX ( SUMMARIZE ( 'Table', 'Date'[Year] ), 'Date'[Year] )
VAR _startingyearcards =
    CALCULATE ( SUM ( 'Table'[Cards] ), 'Date'[Year] = _startingyear )
VAR _latestyearcard =
    CALCULATE ( SUM ( 'Table'[Cards] ), 'Date'[Year] = _latestyear )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Month] ), DIVIDE ( CurrentYearCards - PreviousMonthCards, PreviousMonthCards, 0 ),
        ISINSCOPE ( 'Date'[Year] ), DIVIDE ( CurrentYearCards - PreviousYearCards, PreviousYearCards, 0 ),
        DIVIDE ( _latestyearcard - _startingyearcards, _startingyearcards, 0 )
    )

[derekli17001]

Hello,

Thank you and apologies

Here is my report Sample.pbix

Your calculation has fixed the month by month percentage difference however i noticed the YOY difference seems to be wrong.

As seen in the picture, 2024 seems to be less than 2023 however it shows a posititve increase of 16.68%. Any guidance will help - many thanks!

[Jihwan_Kim]