Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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]))
    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"))))

     

2 Replies

  • 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"))))

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Whitewater100, worked great!!