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
leletran
Frequent Visitor

Calculate days late with +X days

Hi there!

 

I'm trying to calculate if an order was shipped before its due date, then it's "On Time". However, if an order was shipped after its due date, then it is "Late", with a +X day(s) after its due date. For an example, a package was ordered 4/6, and due date is 4/10 - if package was shipped 4/7, it's on time. If it's shipped on 4/11, it's late with a +1 day. If it's shipped on 4/12, it's +2 days and so on. 

 

I have a column called Current Date whose formula is "TODAY()". 

This is my formula to calculate Delivery Status (On Time or Late): 

Screenshot 2023-04-06 at 10.23.26 AM.png

Formula for Days Late:

Screenshot 2023-04-06 at 10.23.35 AM.png

 

I can't seem to get the formula correct because I'll either end up with a negative days late or positive days late, or the calculation is off:

Screenshot 2023-04-06 at 9.47.22 AM.png

Screenshot 2023-04-06 at 10.11.47 AM.png

There's no reason for an order that shipped 11/23/22 whose due date is also 11/23/22 to be +134 days late...  Some of the calculation is correct, such as due date 11/23/2022 and ship date is 4/5/23 with a +134 days.

 

My goal is if an order was shipped before due date, then Delivery Status is On Time, and Days Late column is empty. If order was shipped after due date, then Delivery Status is Late, and a +X day(s) in Days Late. I think the problem is I'm using [Current Date] to substract [Due Date]? Any insight is greatly appreciated! Thank you!

 

Cheers,

Lele

  

1 ACCEPTED SOLUTION
leletran
Frequent Visitor

Okay, figured it out! 

 

Here's the updated formulas:

Delivery Status: 

Screenshot 2023-04-06 at 11.15.18 AM.png

Days Late:

Screenshot 2023-04-06 at 11.15.26 AM.png

End result:

Screenshot 2023-04-06 at 11.15.58 AM.png

 

Much better! I guess coffee and pacing back and forth helped! Hope this helps someone. 😁

View solution in original post

1 REPLY 1
leletran
Frequent Visitor

Okay, figured it out! 

 

Here's the updated formulas:

Delivery Status: 

Screenshot 2023-04-06 at 11.15.18 AM.png

Days Late:

Screenshot 2023-04-06 at 11.15.26 AM.png

End result:

Screenshot 2023-04-06 at 11.15.58 AM.png

 

Much better! I guess coffee and pacing back and forth helped! Hope this helps someone. 😁

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.