Create a max date measure for each id

[Pierre_B]

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	A	01/03/2022
01/02/2022	A	01/03/2022
01/03/2022	A	01/03/2022
01/01/2022	B	01/03/2022
01/02/2022	B	01/03/2022
01/03/2022	B	01/03/2022
01/01/2022	C	01/02/2022
01/02/2022	C	01/02/2022
01/01/2022	D	01/03/2022
01/03/2022	D	01/03/2022

date	id	max date
01/01/2022	A	01/02/2022
01/02/2022	A	01/02/2022
01/01/2022	B	01/02/2022
01/02/2022	B	01/02/2022
01/01/2022	C	01/02/2022
01/02/2022	C	01/02/2022
01/01/2022	D	01/01/2022

Two examples of tables I would like (they are the same, the second one is filtered).

I tried multiple formula, such as:

max date =

var id = SELECTEDVALUE(table[id])

return maxx(filter(table, ALLSELECTED(table[Date]) && table[id]=sec), table[id])

Bu it didn't work.

If someone has an idee, it would be very helpful. 

Thank you,

Pierre

[Thennarasu_R]

Hi !
@Pierre_B 
Use this one
Measure=Calculate(max(Date),Allexcept(Tablename,IDColumn))

 Thanks,
Thennarasu R

[Pierre_B]

Hi @Thennarasu_R,

As replied to @Barthel, it doesn't take into account the filter on 'Date'.

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.

[Pierre_B]

This is what I tried but I get this message: 

"Expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 It is always what I get when I combine filter on selected dates and IDs...

[Barthel · Accepted solution]

@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]

Hi @Barthel,

It works perfectly, thanks a lot !

Have a good day.

[Pierre_B]

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]

@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?

[Barthel]

Hey @Pierre_B,

Give this a try:

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

It calculates the maximum date for each row removing all fitler context of the table, except that of the 'id', so that the maximum date is calculated per 'id'.