Creating Custom Column "status" based on multiple columns.

[OpenMike13]

Hello 

Im having issues trying to find a way to create a custom Status column based on multiple other columns within the same table. I am looking to provide a status on a class shipments based on where its at in our shipping process. 

here is how the data is set up in our database 

Course	Order Received	Date shipped	Date of delivery to course	Date Scheduled for return	Date Returned
XX	3/2/2022	3/5/2022	3/8/2022	3/20/2022	22-Mar
TT	4/2/2022	4/5/2022	4/8/2022	4/20/2022	4/22/2022
AA	4/15/2022	4/18/2022	4/20/2022
DD	4/18/2022	4/25/2022
CC	4/27/2022

Here is what the status column should look like

Course	Order Received	Date shipped	Date of delivery to course	Date Scheduled for return	Date Returned	Status
XX	3/2/2022	3/5/2022	3/8/2022	3/20/2022	22-Mar	Completed/Returned
TT	4/2/2022	4/5/2022	4/8/2022	4/20/2022	4/22/2022	Completed/Returned
AA	4/15/2022	4/18/2022	4/20/2022			Delivered to Course
DD	4/18/2022	4/25/2022				Shipped to Course
CC	4/27/2022					Order Received

Any help is appreciated! thank you 

[PhilipTreacy · Accepted solution]

Hi @OpenMike13 

Download example PBIX file

This will create your column

Status = 

SWITCH(

    TRUE(),

    NOT ISBLANK([Date Returned]), "Completed/Returned",

    NOT ISBLANK([Date of delivery to course]), "Delivered to Course",

    NOT ISBLANK([Date shipped]), "Shipped to Course",

    "Order Received"
)

Note that you don't have any status for something that is scheduled for return, and not yet returned.  But you just need to add another line to the code above for that.

Regards

Phil