Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RickB72
Regular Visitor

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! ☺

 

RickB72_0-1654200567030.png

 

Thanks

Rick

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution 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]))
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"))))
Whitewater100_0-1654205341476.png

 

View solution in original post

2 REPLIES 2
Whitewater100
Solution Sage
Solution 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]))
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"))))
Whitewater100_0-1654205341476.png

 

Thanks Whitewater100, worked great!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors