Dynamic Year Over Year Percent Change

[o59393]

hi all

I need to calculate a year over year percent change of tickets created based on the year I select with a slicer. 

E.g. if I choose 2018 from the slicer, it should compare the 2018 vs 2017 tickets, if I select 2019, compare 2019 vs 2018 ticketsand so on. But if I select blank, it should return blank.

I created a DAX that uses 2019 as a date reference, and a comparisson of this year vs previous year however this is static and compares only 2 years, and I want it to be dynamic depending on the slicer selection.

I attach the PBI 

https://www.mediafire.com/file/wvnpxdyvfcecr70

And the excel

http://www.mediafire.com/file/nrxn7wx75lhzi0i/report1576508921493.xls/file

Is is possible to have a dax with the conditions desctibed above?

 Thank you!

[mwegener · Accepted solution]

Hi @o59393 ,

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[o59393]

WOW!

Looks very solid. Just one thing, how can I get the result as 45.87% instead of 145.87%?

I tried swtiching the numerator and denominator but didnt work.

Change % =

IF (

HASONEVALUE ( report1576508921493[Year] ),

DIVIDE (

[Number of Tickets],

CALCULATE (

[Number of Tickets],

FILTER (

ALL ( report1576508921493[Year] ),

report1576508921493[Year]

= MAX ( report1576508921493[Year] ) - 1

)

),

BLANK ()

)

)

Thanks a mill!!!

[mwegener]

Hi @o59393 

try this.

Change % = 
IF (
    HASONEVALUE ( report1576508921493[Year] );
    DIVIDE (
        [Number of Tickets];
        CALCULATE (
            [Number of Tickets];
            FILTER (
                ALL ( report1576508921493[Year] );
                report1576508921493[Year]
                    = MAX ( report1576508921493[Year] ) - 1
            )
        )
    )-1;
    BLANK()
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[o59393]

You rock, thanks a million for your help!!

Have a great day!