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!

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

 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors