Forum Discussion
Drive Duration Calculation
I am probably making this harder than it needs to be, but I am trying to calculate the driving time between each stop sequence for each order. For example for stop 2 for order 6005581 I need to know how long the drive was from departure time from stop sequence #1 to the arrival time at Stop 2. I thought about using the OFFSET function, but not sure that is the best way to do it? Any help or suggestion would be appreciated? Thanks.
7 Replies
cheid_4838 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.Thanks for the quick response and your help. I think I understand what you provided, but have a couple of questions.
1. What value would I put between the brackets in this statement? VAR __Current = [Value]
2. Since I have multiple orders, how do I make sure that the driver duration is only calculated within each order and that the time won't be calculated across orders like the example I provided where there were two orders?
cheid_4838 Since you want to subtract the previous departure time from the current arrival time, then you would put VAR __Current = [Arrival Date & Time].
You will want to capture the current driver and perhaps order similar to the __Current variable. You then need to use those when filtering to find your previous row.
