Dynamic YoY percentage %

[Anonymous]

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!

[ahadkarimi]

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

[Anonymous]

https://app.powerbi.com/groups/me/reports/b011633c-404f-4297-9b2f-a5eee933e07d/ReportSectioncb9ff538...

let me know if you can't access it.

[ahadkarimi]

Yes, I have no access.

[ahadkarimi]

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

[Anonymous]

Hello @ahadkarimi, 

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

Thank you,

[ahadkarimi]

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

[Anonymous]

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!

[ahadkarimi]

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?

[Anonymous]

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?

[ahadkarimi · Accepted solution]

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. 

[Anonymous]

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!

[ahadkarimi]

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