Forum Discussion

BH22One's avatar
BH22One
Helper I
6 years ago
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 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.
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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 ) )

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      BH22One
      Helper I
      6 years ago

      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.