Delivery Status?

[RickB72]

Hello Everyone 

I'm hoping to get some help on a DAX formula that is above my current skill level

The company I work for has a requirement that supplier delivers must be +/- 3 days form the "Promise Date" otherwise its considered "Received Late" ("Receipt date" is more then 3 days past "Promised Date") or " Received Early" ("Receipt Date" is more then 3 days before "Promised Date".)   

or

If the "Receipt Date" is blank (not yet received) then its "Pending"  or   if the current date is greater then 3 days past "Promised Date" its "Late Not Yet Received"

I wanted to add a new Column called "Status" to show the evaluation result (Received Late, Received Early, Received On Time, Pending, Late Not Yet Received").   I figure I can use a slicer in my visual to filter for a specific "Supplier Name" or "Promise Date" range.   below is a clip of my data table.  Any help/guidance you could provide is greatly appreciated! ☺

Thanks

Rick

[Whitewater100 · Accepted solution]

Hi:

You can try this calc column. I named table "Deliveries" so you can change bolded part to your tables name. You can also use switch statment but I went old school on this one.

Status =

var daysdiff = IF(Deliveries[Received Date] = BLANK(), BLANK(),

INT(Deliveries[Received Date]-Deliveries[Promised Date]))

return

IF(AND(Deliveries[Received Date] = BLANK(), INT(Deliveries[Received Date]-Deliveries[Promised Date]) < 4), "Pending",

IF(daysdiff>3,"Received Late", IF(daysdiff <3,"Received Early",IF(daysdiff IN {-1,-2,-3,1,2,3},"Recieved On Time",

"Late Not Yet Received"))))

[RickB72]

Thanks Whitewater100, worked great!!