Hi all
Some context: I need to sum some data based on a year slicer. For a big portion of the year, the slicer goes 1-2 years further than there is data in that context (intended). So for example Slicer goes to Year=2022, but there is only data available until 2020.
What I need to do now is Slice the data by year if there is data for that year (normal behaviour), BUT if there isn't, default to the latest year (in this example 2020). I wrote a measure that really should work by my understanding but it simply doesn't, and i can't figure out why:
Total=
VAR Slicer = [SlicerSelectedYear]
VAR DataYear = [AvailableDataYear]
RETURN
IF(
Slicer <= DataYear,
SUM(dataTable[ENT]),
CALCULATE(
CALCULATE(
SUM(dataTable[ENT]),
dataTable[Year] = DataYear
),
ALL(dataTable[Year])
)
)
The 2 Variables Slicer and DataYear receive the correct values. The normal behaviour case also works and returns the sum for the selected year. But the alternate case with the Calculate simply returns Blank no matter what I do. My intention behind this code was to first remove the filter on [Year] imposed by the slicer, e.g. 2021, and then filter on [Year] = DataYear, e.g. 2020, instead.
Any help is greatly appreciated.
