Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
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

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 works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

4 REPLIES 4

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 works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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 works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


You rock, thanks a million for your help!!

 

Have a great day!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.