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

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/

here is the link:

https://drive.google.com/file/d/1MfOo9ZGu0Omc3QO55B1ts3rLkUcNqDb4/view?usp=sharing

     i am looking for the result something like this, and whenever i choose something from a slicer it should filter the table based on slicer order. 

 

Typeqty
Smartphones65
VIVO4
Motorola5
LG7
Samsung G226
Lenovo8
Apple Iphone 137
One Plus10
Huawei5
Nokia9
Xiaomi4
Laptop39
Apple5
Toshiba9
Asus7
HP1
Dell5
Samsung4
Acer8

 

Thanks in advance!

I do not understand your question at all.  May be someone else can help you.


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

Okay, sorry for taking your time but let me try one more time please. I have a dataset which looks like below and would like to find the last qty of each product based on id:

 

 

id            Product          qty  
10001Lenovo1
10002VIVO4
10003Huawei5
10004Lenovo8
10005VIVO9

 

and i would like to see out put like this:

 

 

id            Product         qty   
10003Huawei5
10004Lenovo8
10005VIVO9

 

i have tried function like this below but the result is in matrix table is empty cells

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

and then:

=if(HASONEFILTER(Table[Product]),LOOKUPVALUE(Table[qty],Table[id],[Latest value],Table[Product],VALUES(Table[Product])),BLANK())

 

i would really appreciate if you could help me with this issue. Thank you!

Hi,

Try these measures

Max ID = MAX(Data[id])
Qty at Max ID = CALCULATE(SUM(Data[qty]),FILTER(VALUES(Data[id]),Data[id]=CALCULATE([Max ID],all(Data[id]))))

Hope this helps.

Untitled.png


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

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.