Get the last status of a product

[Tybaal]

Hello all, 

I try to get the last status of a product when I select a range of date.

Example :

Product	Status	Value	Date
1	O	10	Nov, 2020
1	P	15	Dec, 2020
	Total Mvt	5

I have these two date because I choose it in a slicer.

What I like to see is, when I remove the date column :

Product	Status	Value
1	P	5

I'd like to get the stastus correponding to the latest date...

It can be look simple but I cannot reach that...

Someone can help me?

Thank you in advance.

[PaulDBrown]

@Tybaal 

Try:

1)

Value on last date = 

VAR _prod = MAXX(ALLEXCEPT(Table, Table [Product]), MAX(Table[date]))
RETURN
CALCULATE([Sum of Value], 
           FILTER(Table, Table [date] = _prod))

2)

Status on last date =

VAR laststatus = CALCULATE([Value on last date], ALL(Table[Status]))
VAR _Prod = MAXX(ALLEXCEPT(Table, Table [Product]), MAX(Table[date]))
RETURN
CALCULATE(MAX(Table[Status]), 
           FILTER(ALL(Table), 
            [Value on last date] = laststatus && 
             Table [date] = _Prod))

Create your visual with the Table [Product] field and these two measures.

[Pragati11]

Hi @Tybaal ,

You can try something as follows by creating a measure:

LastStatus = 

var lastDate = CALCULATE(LASTDATE(tablename[Date]))

RETURN

CALCULATE(MAX(tablename[Status]), FILTER(tablename, tablename[Date] = lastDate))

Let me know if this works. (Replace tablename in the above DAX with your table name)

Thanks,

Pragati

[Tybaal]

Hello and thank you for your help!! 

I tried Pragati's solution and I success to get the last status but I have another little thing to resolve

Now I have this result which is closer of what I want :

Even if I removed columns "Status" and "Date" I have :

Is there a way to apply the LastStatus value to all my rows and finally get this :

Thank you in advance.

[amitchandak]

@Tybaal , Value in the table above is not matching with the one below

Try like for latest value

lastnonblankvalue(table[date], max(Table[Value]))