cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Welsh_Will
Helper II
Helper II

Time calculation

Hi,

 

Another query on calculating dates/times, but I haven't seen anything similar to what I'm trying to do, so I'd appreciate any help!

 

When we place an order with a carrier, we speicify the delivery date/time that we want. We have cut off times agreed with our carriers for when we will place the order for them to then deliver by the requested date, and I need to see how often we are failing to hit that cut off.

 

The data is set by times in one column and day number in another; today is Day 0, tomorrow is Day 1, next day is Day 2 etc

 

 Order Cut OffDayDelivery Time
Point A to Point B10:00116:00
Point A to Point C14:00209:00

 

In this example, if we want an item delivering to Point B tomorrow, we would need to book the order by 10:00 today.  Point C is further away - we need to book before 14:00 Day 0 to ensure delivery at 09:00 Day 2.

 

Essentially, Point B requires 30 hours lead time, point C needs 43 hours. How do I calculate that from this date in a new column?

 

Many thanks!!

2 ACCEPTED SOLUTIONS
nsrshkh1
Resolver IV
Resolver IV

@Welsh_Will 

 

Thanks for detailed explaination

 

Use below formula in Calculated Column

 

Spoiler
Duration (Hours) = 
Var StartDate = TODAY()+'Table'[Order Cut Off]
Var EndDate = TODAY()+'Table'[Delivery Time] + 'Table'[Day]
RETURN
(EndDate - StartDate) * 24


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be Datanaut!

View solution in original post

Hi @Welsh_Will 

If Delivery Time and Order Cut Off are formatted as Time and Day as whole number, you can simply do

 

NewColumn = 24*(Table1[Delivery Time] + Table1[Day] - Table1[Order Cut Off] )

 

 

View solution in original post

3 REPLIES 3
nsrshkh1
Resolver IV
Resolver IV

@Welsh_Will 

 

Thanks for detailed explaination

 

Use below formula in Calculated Column

 

Spoiler
Duration (Hours) = 
Var StartDate = TODAY()+'Table'[Order Cut Off]
Var EndDate = TODAY()+'Table'[Delivery Time] + 'Table'[Day]
RETURN
(EndDate - StartDate) * 24


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be Datanaut!

View solution in original post

Thank you!

Hi @Welsh_Will 

If Delivery Time and Order Cut Off are formatted as Time and Day as whole number, you can simply do

 

NewColumn = 24*(Table1[Delivery Time] + Table1[Day] - Table1[Order Cut Off] )

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.