Forum Discussion
Help with multiple SELECTED VALUE for YOY
I have created the following formula to get specific calculations shown when one date in my date slicer is selected:
These are working correctly. However, what I'm actually trying to do is a dynamic YOY calculation like this:
- If "Jan 2025" and "Jan 2024" is selected, then DIVIDE((CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2025"))-(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Jan 2024")),(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Jan 2024")),0),
- If "Dec 2024" and "Dec 2023" is selected, then DIVIDE((CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Dec 2024"))-(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Dec 2023")),(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Dec 2023")),0))
- If nothing is selected, then default view would be the first bullet point: DIVIDE((CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2025"))-(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Jan 2024")),(CALCULATE(SUM('Table'[Cost]),'Table'[Date] = "Jan 2024")),0),
Can you please help me figure the right formula out? I;ve tried using "&&", ";", and "II" to be able to do multiple SELECTEDVALUE instead of single SELECTED VALUE but nothing is working. Thank you for your time
Hi Anonymous - you'll need to handle multiple date selections properly. The SELECTEDVALUE function works only when a single value is selected, but in your case, you need to work with multiple selections.
YoYCostDifference =
VAR SelectedDates = VALUES('Table'[Date])
VAR MaxDate = MAXX(SelectedDates, 'Table'[Date])
VAR MinDate = MINX(SelectedDates, 'Table'[Date])VAR CurrentYearCost = CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = MaxDate)
VAR PriorYearCost = CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = MinDate)RETURN
IF (
COUNTROWS(SelectedDates) = 2,
DIVIDE(CurrentYearCost - PriorYearCost, PriorYearCost, 0),
// Default case if nothing or only one date is selected
DIVIDE(
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2025") -
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2024"),
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2024"),
0
)
)Hope this works.
2 Replies
- rajendraongole1
Super User
Hi Anonymous - you'll need to handle multiple date selections properly. The SELECTEDVALUE function works only when a single value is selected, but in your case, you need to work with multiple selections.
YoYCostDifference =
VAR SelectedDates = VALUES('Table'[Date])
VAR MaxDate = MAXX(SelectedDates, 'Table'[Date])
VAR MinDate = MINX(SelectedDates, 'Table'[Date])VAR CurrentYearCost = CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = MaxDate)
VAR PriorYearCost = CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = MinDate)RETURN
IF (
COUNTROWS(SelectedDates) = 2,
DIVIDE(CurrentYearCost - PriorYearCost, PriorYearCost, 0),
// Default case if nothing or only one date is selected
DIVIDE(
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2025") -
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2024"),
CALCULATE(SUM('Table'[Cost]), 'Table'[Date] = "Jan 2024"),
0
)
)Hope this works.