Display data form Max Year in data set

[BH22One]

Good Afternoon,

I am struggling to create a Custom Measure that will calculate the sum of a column while filtering on the Maximum Year Number in a difference column within the same table.

I am currently using the following DAX Expression: 

Max Year Sales = CALCULATE(SUM('Product'[Units]),FILTER('Product','Product'[Year]=MAX('Product'[Year])))

The result is that all Years still appear in my Matrix Visual.

When I change the expression to filter a specific value (i.e. 2019) it works as I intend it to:

Max Year Sales = CALCULATE(SUM('Product'[Units]),FILTER('Product','Product'[Year]=2019))

The problem with the 2nd expression is that a developer would have to change this manual each year. I'd rather the max change automatically once a new year is introduced. In addition, if a user selects 2018 and 2017 in a slicer, ideally the data would filter to 2018 since it is the Maximum Year selected.

Any assistance would be appreciated.

[jdbuchanan71 · Accepted solution]

Hello @BH22One 

Give this a try.  It uses ALL ( Product ) to clear the filter context then calc the max year in a variable.  Then it uses the variable in the calc.  The KEEPFILTERS is to stop the amount from appearing in the other years.

Max Year Sales = 
VAR MaxProductYear = CALCULATE ( MAX ( 'Product'[Year] ) , ALL ( 'Product' ) )

RETURN
CALCULATE(
    SUM('Product'[Units]),
    KEEPFILTERS( 'Product'[Year]  = MaxProductYear ) )

[BH22One]

Hi @jdbuchanan71 ,

Your solution worked perfect! I was also able to tweak it just a tad so that it changes in case I want to select a previous year in a slicer. I used ALLEXCEPT so that the variable would recognize a new max year in case I select a different year in the slicer.

Max Year Sales =

VAR MaxProductYear = CALCULATE ( MAX ( 'Product'[Year] ) , ALLEXCEPT('Product','Product'[Year]) )

RETURN

CALCULATE(

SUM('Product'[Units]),

KEEPFILTERS( 'Product'[Year] = MaxProductYear ) )

Thanks a ton for your input.