Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

3 Replies

  • philouduv's avatar
    philouduv
    Icon for Resolver III rankResolver III
    3 years ago

    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,

     
  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.