Check the previous column value, with default status

[king2005r]

Hi Everyone, 

I need your help, I have very sample request

I want the formula to check if there any previous "active" orders for the same customer ID that has "Active" status so the final output will be "Not yet" status regardless of any other status

Data does not include any date. 

Example :

Customer ID	Order Number	Customer name	Status
1	14456	Johan	Active
1	14876	Johan	Closed
2	11222	Test1	Active
2	11484	Test1	Close
2	11454	Test1	Close
3	17461	Sarah	Close

Output : 

Customer ID	Order Number	Customer name	Status	All customer package deliver
1	14456	Johan	Active	Not yet
1	14876	Johan	Closed	Not yet
2	11222	Test1	Active	Not yet
2	11484	Test1	Close	Not yet
2	11454	Test1	Close	Not yet
3	17461	Sarah	Close	Yes

[tamerj1]

Hi @king2005r 

You can use the following for a calculated column 

All Customer Package Delivered =
IF (
    COUNTROWS (
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
                && Data[Status] = "Active"
        )
    ) > 0,
    "Not yet",
    "Yes"
)

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calculated measure.

All customers package deliver: =
IF (
HASONEVALUE ( Data[Customer ID] ),
IF (
"Active"
IN CALCULATETABLE (
VALUES ( Data[Status] ),
ALLEXCEPT ( Data, Data[Customer ID] )
),
"Not yet",
"Yes"
)
)

[king2005r]

Hi 

Thank you for your reply, the attached solution is measurement, can I have it as a column? 

[Jihwan_Kim · Accepted solution]

All customers package deliver CC =
IF (
"Active"
IN SUMMARIZE (
FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
Data[Status]
),
"Not yet",
"Yes"
)

[king2005r]

Thank you so much, this is work fine 🙂 appreciated!