DAX help - Get latest value for each ID

[indhu]

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

[Ashish_Mathur]

Hi,

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

[Ismail94]

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. 

Type	qty
Smartphones	65
VIVO	4
Motorola	5
LG	7
Samsung G22	6
Lenovo	8
Apple Iphone 13	7
One Plus	10
Huawei	5
Nokia	9
Xiaomi	4
Laptop	39
Apple	5
Toshiba	9
Asus	7
HP	1
Dell	5
Samsung	4
Acer	8

Thanks in advance!

[Ashish_Mathur]

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

[Ismail94]

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
10001	Lenovo	1
10002	VIVO	4
10003	Huawei	5
10004	Lenovo	8
10005	VIVO	9

and i would like to see out put like this:

id	Product	qty
10003	Huawei	5
10004	Lenovo	8
10005	VIVO	9

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]

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.