topic Re: Dynamic YoY percentage % in DAX Commands and Tips

Dynamic YoY percentage %

Anonymous — Tue, 13 Aug 2024 15:01:46 GMT

Hello all,

I've searched mulitple posts on this forum to try to solve this problem and I have had no luck so far.

I want to be able to calculate a percentage change of different fiscal years based on the slicer selection. However, I keep getting this (Blank) error when I select different years. When the same year is selected, the value is 100%.

I edited the interactions on the slicers so that I can select multiple years referencing the same calendar (which is called 'xFY Calendar').

any help/suggestions would be appreciated.

Thank you!

Re: Dynamic YoY percentage %

ahadkarimi — Tue, 13 Aug 2024 19:31:51 GMT

Hey Anonymous, try this code, and if you encounter any issues, let me know.

YoY % = IF( ISBLANK([RiderSelectedYear]) || ISBLANK([RiderSelected2ndYear]), BLANK(), DIVIDE([RiderSelectedYear], [RiderSelected2ndYear], 0) )

Re: Dynamic YoY percentage %

Anonymous — Tue, 13 Aug 2024 19:46:25 GMT

Hello ahadkarimi,

I'm still getting the same error. Let me know if you need me to share my dashboard.

Thank you,

Re: Dynamic YoY percentage %

ahadkarimi — Tue, 13 Aug 2024 19:48:37 GMT

Anonymous yes, please share your dashboard, and I'll help you troubleshoot the error.

Re: Dynamic YoY percentage %

Anonymous — Tue, 13 Aug 2024 20:00:57 GMT

https://app.powerbi.com/groups/me/reports/b011633c-404f-4297-9b2f-a5eee933e07d/ReportSectioncb9ff538a6edec19730c?experience=power-bi

let me know if you can't access it.

Re: Dynamic YoY percentage %

ahadkarimi — Tue, 13 Aug 2024 20:07:50 GMT

Yes, I have no access.

Re: Dynamic YoY percentage %

ahadkarimi — Tue, 13 Aug 2024 20:14:05 GMT

you can share it in microsoft onedrive or google drive and put it here or send it by mail to ahad.karimi8@gmail.com

Re: Dynamic YoY percentage %

Anonymous — Wed, 14 Aug 2024 13:11:47 GMT

let me know if this works, I saved it onto a google drive.

https://drive.google.com/file/d/1il0EgIIT4H_8D7t6bzuim_vALyTxRpKH/view?usp=sharing

Thanks!

Re: Dynamic YoY percentage %

ahadkarimi — Wed, 14 Aug 2024 18:07:57 GMT

Hi Anonymous, could you please check the RiderSelectedYear and RiderSelected2ndYear measures to see if they are correct?

for testing, you can add a table from the Visualization pane, include these two measures in the table, and review the values according to the slicer selections (are those correct?).

I suspect that the issue might be with the RiderSelectedYear and RiderSelected2ndYear measures. Could you verify and let me know if any problems still persist?

Re: Dynamic YoY percentage %

Anonymous — Wed, 14 Aug 2024 18:40:29 GMT

Hello, I just checked and the measures don't show up on the table if both slicers are selected. Do you have an alternative solution to the RiderSelect measures? do you think I need to reference another data set since it appears that selecting two years from the same dataset cancels them out?

Re: Dynamic YoY percentage %

ahadkarimi — Thu, 15 Aug 2024 03:30:42 GMT

Hey Anonymous, make the following changes for the 2nd year:

RiderSelected2ndYear = VAR SelectedYearNumber = VALUE(RIGHT(SELECTEDVALUE('xFY Calender'[FY]), 2)) VAR PreviousYearNumber = SelectedYearNumber - 1 VAR PreviousFY = "FY" & FORMAT(PreviousYearNumber, "00") RETURN CALCULATE( [TotalRiders], FILTER( ALL('xFY Calender'[FY]), 'xFY Calender'[FY] = PreviousFY ) )

if you would like to display a dash (-) instead of 'Blank' in the card, use the code below:

YoY % = IF( ISBLANK([RiderSelectedYear]) || ISBLANK([RiderSelected2ndYear]), "-", DIVIDE([RiderSelectedYear], [RiderSelected2ndYear], 0) )

Please let me know what happend and if there are any issues.

Re: Dynamic YoY percentage %

Anonymous — Thu, 15 Aug 2024 15:04:02 GMT

Hello, this solution above works. I was hoping that the YoY% measure would be able to work by selecting any fiscal year instead of only the year prior but I suppose Power BI is limited in that way.

Thank you so much for your effort and your steady communication with me!

Re: Dynamic YoY percentage %

ahadkarimi — Thu, 15 Aug 2024 21:33:35 GMT

Anonymous you're very welcome! I'm happy to help and always here whenever you need me.