Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Off | Day | Delivery Time | |
Point A to Point B | 10:00 | 1 | 16:00 |
Point A to Point C | 14:00 | 2 | 09: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!!
Solved! Go to Solution.
@Anonymous
Thanks for detailed explaination
Use below formula in Calculated Column
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!
Hi @Anonymous
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] )
@Anonymous
Thanks for detailed explaination
Use below formula in Calculated Column
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 @Anonymous
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |