topic Re: last status in DAX Commands and Tips

last status

Anonymous — Wed, 30 Nov 2022 11:16:51 GMT

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!!!

Re: last status

philouduv — Wed, 30 Nov 2022 13:44:39 GMT

Hello Anonymous ,

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,

Re: last status

FreemanZ — Wed, 30 Nov 2022 14:28:18 GMT

Hi Anonymous

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:

Re: last status

Anonymous — Thu, 01 Dec 2022 07:44:33 GMT

Hi Anonymous

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.