Forum Discussion

Pierre_B's avatar
Pierre_B
New Member
3 years ago
Solved

Create a max date measure for each id

Hi everyone,   I would like to create a measure using dax in order to get the max date for each id and filtered date, and keeping the every date in my table.   date id max date 01/01/2022...
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    Pierre_B,

    Try using a variable. For me that seems to do the trick.  

     

     

    max date = 
VAR _dates =
    ALLSELECTED ( 'Table'[date] )
VAR _result =
    CALCULATE (
        MAX ( 'table'[date] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[id] ),
        _dates
    )
RETURN
    _result

     

     

     

Pierre_B's avatar
Pierre_B
New Member
3 years ago

Hi Barthel, 

 

Thank you for your response. I am not getting what is expected, as I would like to have the max date of the selected dates. So I need to keep the filter on 'Date'.

 

In my example (2), I filtered 'Date' to keep Jan & Feb and the 'max date' showed Feb as max date for ID A, B, C and Jan for D. 

 

With ALLEXCEPT, I get the max date among all 'Date'. 

 

Let me know if you have any ideas.

  • Barthel's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Pierre_B,

    Oh yes I see what you mean. You can make the calculation take into account the selected dates by using the ALLSELECTED formula. This removes the filter context from within the visual (the rows of the matrix), and keeps the filter context from outside the visual (for example, a slicer). 

     

    max date =
CALCULATE (
    MAX ( 'table'[date] ),
    ALLEXCEPT ( 'table', 'table'[id] ),
    ALLSELECTED ( 'table'[date] )
)

     

    Does this work?