Forum Discussion
Solved
Display data form Max Year in data set
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 c...
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 ) )
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 ) )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]) )RETURNCALCULATE(SUM('Product'[Units]),KEEPFILTERS( 'Product'[Year] = MaxProductYear ) )Thanks a ton for your input.
