last status

[Krijgerss21]

hello,

i have a data set of custumers status en date and i only want to show the last status of thath customer for example:

customer A      status open             date 1-1-2020
customer C      status closed           date 6-1-2020
customer B      status closed           date 5-1-2020
customer A      status defintive       date 3-1-2020
customer C      status open             date 4-1-2020
customer A      status Closed          date 6-1-2020

i my powerbi tables it shows now everything but i only want the last status so for

customer A i want the status Closed
Customer C status closed 

and the other status i dont want to show so if on 8-1-2020 the status of customer a changes i want the status of 8-1-2020 for custmor A

hope someone can help me!!!

[Ailsa-msft · Accepted solution]

Hi @Krijgerss21 

Create a measure like :

latest_status = 
var _maxdate=MAX('Table'[date])
return CALCULATE(SELECTEDVALUE('Table'[status]),ALLEXCEPT('Table','Table'[customer]),'Table'[date]=_maxdate)

Then you will get a result like below :

When you add new row for A and the status is open , then you will get a result like this :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

Hi @Krijgerss21 

there are multiple ways to do so, you may create a column with such code:

LatestStatus =

VAR CurrentCustomer = Customer[customer]

VAR MaxDate =

MAXX(

    FILTER(Customer, Customer[customer]=CurrentCustomer),

    Customer[Date]

)

RETURN

CALCULATETABLE(

    VALUES(Customer[status]),

    Customer[customer] = CurrentCustomer,

    Customer[Date] = MaxDate,

    ALL()

)

I tried and it worked like this:

in Table Visual:

[philouduv]

Hello @Krijgerss21 ,

Create a measure like this 

latest_status = CALCULATE(SELECTEDVALUE(Table3[status]),FIlter(Table3,

Table3[date] = Max(Table3[date])

))
And then in a table add just  customer and this new field
If you want still the date create a second measure like this 

latestdate = CALCULATE(SELECTEDVALUE(Table3[date]),FIlter(Table3,

Table3[date] = Max(Table3[date])

))

And add it to the table

Best regards,