Forum Discussion
Dynamic YoY percentage %
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.
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?
Solution SpecialistHey 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.
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.