March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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.
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.
@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.
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.
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
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]))
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 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.
Hi,
This one works
=if(HASONEFILTER(Table1[Name]),LOOKUPVALUE(Table1[Value],Table1[Date],[Latest value],Table1[Name],VALUES(Table1[Name])),BLANK())
Hope this helps.
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])
Thank you so much sir! Additional thanks for your quick response!))
You are welcome.
I'm doing the same calculation except with DeviceID and DriverName. It keeps giving me the error
Hi,
Share some data, describe the question and show the expected result.
How did you calculate the latest value?
Hi,
This is an old post. Share some data, explain the question and show the expected result.
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
125 | |
85 | |
69 | |
54 | |
45 |
User | Count |
---|---|
204 | |
106 | |
98 | |
65 | |
54 |