Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago
Solved

Dynamic Year Over Year Percent Change

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!

Need Help
Show and Tell
  • mwegener's avatar
    mwegener
    6 years ago

    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.

4 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    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's avatar
      o59393
      Post Prodigy
      6 years ago

      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's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        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.