Forum Discussion

indhu's avatar
indhu
Icon for Helper III rankHelper III
7 years ago
Solved

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 --------------------------------------- ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    This one works

     

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

     

    Hope this helps.

     

Ismail94's avatar
Ismail94
Frequent Visitor
4 years ago

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!

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

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.