Forum Discussion

Mirithu's avatar
Mirithu
Icon for Helper II rankHelper II
4 years ago
Solved

Get Most Recent Date and Amount

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:

DateNameAmount
06/09/2021An Other5899
09/11/2021An Other178
15/01/2021Jane Doe800
30/07/2021Jane Doe6400
22/02/2021John Smith66
23/05/2021John Smith2000

 

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 DateNameAmount
09/11/2021An Other6077
30/07/2021Jane Doe7200
23/05/2021John Smith2066

 

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

 

Max DateNameAmount
09/11/2021An Other178
30/07/2021Jane Doe6400
23/05/2021John Smith2000

 

Thank you.

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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 )

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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 )