Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamic YoY percentage %

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 ...
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    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. 

ahadkarimi's avatar
ahadkarimi
Icon for Solution Specialist rankSolution Specialist
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        ahadkarimi
        Icon for Solution Specialist rankSolution Specialist
        2 years ago

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