Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
indhu
Helper III
Helper III

DAX 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

1 ACCEPTED SOLUTION

Hi,

 

This one works

 

=if(HASONEFILTER(Table1[Name]),LOOKUPVALUE(Table1[Value],Table1[Date],[Latest value],Table1[Name],VALUES(Table1[Name])),BLANK())

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

25 REPLIES 25
Anonymous
Not applicable

Hey, nice solution.

What about situation where there are mutliple values and we want to have a sum of them by each category and latest date?

 

thanks 🙂

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Ashish_Mathur
Super User
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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Anonymous
Not applicable

For Ashish's second DAX, use SELECTEDVALUE to replace VALUES, this time, it should work.

 

 

Last_Value = LOOKUPVALUE(Data[Value],Data[Date],[Last_Date],Data[User Name],SELECTEDVALUE(Data[User Name]))

2018-10-19_10-33-45.png

 

 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur Getting a calculation error.

 

A table of multiple values supplied where a single value was expected. 

 Used same measure as provided not sure why it is not working for me 😞  

 

@Anonymous If I use as suggested by you nothing gets displayed 

 

Thanks,
Indhu

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur please find the attachment for the file.

 

test.pbix

 

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Great work sir! I used your example and works great, except in my case additionally i need to sum the results. As i can see even on that table where you demonstrateded the results, is not calculating the total value of 23+32, could you help me with that case too, please!  Thanks in advance.

Thank you.  Drag this measure

Measure = SUMX(VALUES(Table1[Name]),[Value on last date])

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much sir! Additional thanks for your quick response!))

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'm doing the same calculation except with DeviceID and DriverName. It keeps giving me the error 2021-12-09 09_29_39-.png

Latest value = CALCULATE(LASTNONBLANK(Transflo[Driver Name],""),FILTER(Transflo,MAX(Transflo[Date])))
Driver = IF(HASONEFILTER(Transflo[DeviceID]),LOOKUPVALUE(Transflo[Driver Name],Transflo[Date],[Latest value],Transflo[DeviceID],VALUES(Transflo[DeviceID])),BLANK())

Hi,

Share some data, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

How did you calculate the latest value?

Hi,

This is an old post.  Share some data, explain the question and show the expected result. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello sir!

Last time when I used your function due to my case i was using it by mixing with my own function.

This time i need exactly what is in here, but when i used it i am not being able to handle few issues.  

Latest value = CALCULATE(LASTNONBLANK(Table[value],""),FILTER(Table,MAX(Table[Date])))

and then

=if(HASONEFILTER(Table1[Name]),LOOKUPVALUE(Table1[Value],Table1[Date],[Latest value],Table1[Name],VALUES(Table1[Name])),BLANK())

 

the result is showing all blank in matrix table.

Could you help me with this issue if I share my file , please?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.