Forum Discussion
Delivery Status?
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
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]))returnIF(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"))))
2 Replies
- Whitewater100Solution Sage
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]))returnIF(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"))))- AnonymousNot applicable
Thanks Whitewater100, worked great!!