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.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"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.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"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.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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