cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krijgerss21
Frequent Visitor

last status

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

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

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 :

Ailsamsft_0-1669880562406.png

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

Ailsamsft_1-1669880610588.png

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.

 

View solution in original post

3 REPLIES 3
Ailsa-msft
Community Support
Community Support

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 :

Ailsamsft_0-1669880562406.png

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

Ailsamsft_1-1669880610588.png

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
Community Champion
Community Champion

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:

FreemanZ_0-1669818423417.png

in Table Visual:

FreemanZ_1-1669818461871.png

 

philouduv
Resolver II
Resolver II

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,

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors