cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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!

1 ACCEPTED SOLUTION
Super User II
Super User II

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

4 REPLIES 4
Super User II
Super User II

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

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!!!

 

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


You rock, thanks a million for your help!!

 

Have a great day!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors