How to create a measure that sums only the values of the most recent date.

[kishcolor]

We have the following data.
Among them, we want to extract only the latest value for each user and sum it up.

I tried to combine the TopN, RANKX and Filter functions, but could not get it to work.

If you have any good ideas, could you please let me know?

Best regards,

kishcolor

[vojtechsima · Accepted solution]

Hi, @kishcolor 
I believe this measure could do it:

LatestSum =
var latestDate= CALCULATE(MAX(Table[Date]),
ALLEXCEPT(Table, Table[User]))
// optional - var latestSum = SUMX(FILTER(Table, Table[Date]=latestDate),Table[Amount]) // optional

return latestDate

If you seek a tailor-made solution for your specific table, please paste a copyable sample data.

[kishcolor]

@vojtechsima 

When I use your formula, it only shows of the latest values, is it possible to sum the latest values for each user?

"The "300" is the most recent value of the whole, but we actually want to display the most recent value for each user, which is "930.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@kishcolor , Try a measure like

Measure =
VAR __id = MAX ('Table'[User] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[User] = __id )
CALCULATE ( sum ('Table'[Amount] ), VALUES ('Table'[User] ),'Table'[User] = __id,'Table'[Date] = __date )

or

CALCULATE ( lastnonblankvalue('Table'[Date], sum ('Table'[Amount] )),allexcept('Table', 'Table'[User]))

[kishcolor]

@amitchandak 

Thank you for your answer.

Using your formula, I see the last user's total (=90), but is it possible to show the sum of each user's values (=930)?