Forum Discussion
Helper IIIDAX help - Get latest value for each ID
Hi all,
I have a dataset which looks like below and would like to find the last value in each column for each user.
User Name | Value | Date
---------------------------------------
x 2 12/01/2018
x 1 13/01/2018
y 9 11/01/2018
Output like this
User Name | Value | Date
---------------------------------------
x 1 13/01/2018
y 9 11/01/2018
I tried using dax using last nonblank but I am missing something.
latest value = CALCULATE(LASTNONBLANK(table[value,MAX(Table[Date])))
Also, tried this
Latest value = CALCULATE(LASTNONBLANK(Table[value],""),FILTER(Table,MAX(Table[Date])))
Can somebody please help?
Thanks,
Indhu
Hi,
This one works
=if(HASONEFILTER(Table1[Name]),LOOKUPVALUE(Table1[Value],Table1[Date],[Latest value],Table1[Name],VALUES(Table1[Name])),BLANK())
Hope this helps.
25 Replies
- Ashish_Mathur
Super User
Hi,
These 2 measures work fine
Last date = LASTNONBLANK(Data[Date],1)
Value on last date = LOOKUPVALUE(Data[Value],Data[Date],[Last date],Data[User Name],VALUES(Data[User Name]))
Hope this helps.
indhuThanks Ashish_Mathur
the first measure works as expected. however, when using the second measure I am not able to convert the name to value and don't know how to proceed further.
Value on last date = LOOKUPVALUE(Data[Value],Data[Date],[Last date],Data[User Name],VALUES(Data[User Name]))
1. Is it meant to convert name to text?
2. Did you mean value? even that doesn't work
This part doesn't work Can you please help?
Thanks again,
Indhu
- Ashish_Mathur
What do you mean by "I am not able to convert the name to value"? What error does my formula throw up. The VALUES() function will return a distinct list of Usernames. SInce in a row, only one name can appear, it will return only a single value.
- Ashish_Mathur
Hi,
Share some data and show the expected result.
Ashish_Mathur , here is link where I have described my case:
Honestly, I'm not sure if it's possible by only using calculated measures.
Would be gratefull if you take a look.
