How do you dynamically calculate value based on slicer

[PhilippeGR]

Please bear in mind that I am fairly new at DAX.

I have a table called BaseTable as follows

and a slicer

I have defined 4 measures:

[MaxYr] = CALCULATE(max('BaseTable'[UWYear]), ALLSELECTED('BaseTable'))

[Value MaxYR] = CALCULATE([Value], FILTER(ALL('BaseTable'), 'BaseTable'[UWYear] = [MaxYr]))

[MaxYr-1] = [MaxYr] - 1

[Value MaxYR-1] = CALCULATE([Value], FILTER(ALL('BaseTable'), 'BaseTable'[UWYear] = [MaxYr-1]))

[MaxYR] gives me the max UWYear from the Slicer.

When I choose a UWYear value of 2025, I get the following

Which is correct

However, I change a UWYear value to 2024, I get the following

Which is not correct.

I was expecting [Value MaxYR] to be $1,177 and [Value MaxYR-1] to be BLANK.

Can you please help?

[johnt75 · Accepted solution]

The issue is the ALL('BaseTable') in the FILTER arguments. That makes every year visible, and so when you call ALLSELECTED that will also return all the years.

Also, it is bad practice to filter entire tables, you should only filter the columns you need to. It is more accurate and more efficient.

Change your [Value MaxYr] function to

Value MaxYR =
VAR MaxYear = [MaxYr]
VAR Result =
    CALCULATE ( [Value], 'BaseTable'[UWYear] = MaxYear )
RETURN
    Result

and make the corresponding changes in [Value MaxYR-1] as well.