Get Most Recent Date and Amount

[Mirithu]

Hello,

I am trying to write a DAX measure that will give me the most recent date and the corresponding values for that date from a data set below:

Date	Name	Amount
06/09/2021	An Other	5899
09/11/2021	An Other	178
15/01/2021	Jane Doe	800
30/07/2021	Jane Doe	6400
22/02/2021	John Smith	66
23/05/2021	John Smith	2000

I have the following measure:

Max Date =

MAXX(

FILTER( ALL('Data' ), 'Data'[Name] = SELECTEDVALUE('Data'[Name] )),

'Data'[Date]

)

When I add it to the table, I get the most recent date, but the amount is added up.

Max Date	Name	Amount
09/11/2021	An Other	6077
30/07/2021	Jane Doe	7200
23/05/2021	John Smith	2066

How do I write the measure to get the distinct values for the most recent dates i.e.

Max Date	Name	Amount
09/11/2021	An Other	178
30/07/2021	Jane Doe	6400
23/05/2021	John Smith	2000

Thank you.

[Mirithu]

Works perfectly! Thank you.

[amitchandak · Accepted solution]

@Mirithu , try a measure like

count Measure =
VAR __id = MAX ('Table'[Name] )
VAR __date = CALCULATE ( MAX('Table'[DAte] ), ALLSELECTED ('Table' ), 'Table'[Name] = __id )
CALCULATE ( sum ('Table'[Amount] ), VALUES ('Table'[Name] ),'Table'[Name] = __id,'Table'[DAte] = __date )