Drive Duration Calculation

[cheid_4838]

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.  

[Greg_Deckler]

@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/3395....
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.

[cheid_4838]

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?   

[Greg_Deckler]

@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.

[cheid_4838]

I must be missing something because I keep getting a syntax error message.  What am I missing?  

Table = stops

Drive Duration =

VAR CURRENT = stops[stp_arrivaldate]

VAR PreviousDate = MAXX(FILTER(stops,stops[stp_departuredate] < EARLIER(stops[stp_departuredate]

VAR Previous = MAXX(FILTER(stops,stops[stp_departuredate]= PreviousDate,stops[stp_arrivaldate])

RETURN

(_Current - _Previous)*1

[Greg_Deckler]

@cheid_4838 

Drive Duration =

VAR CURRENT = stops[stp_arrivaldate]

VAR PreviousDate = MAXX(FILTER(stops,stops[stp_departuredate] < CURRENT), [stp_departuredate]) 


RETURN
(_Current - _PreviousDate)*1.

[cheid_4838]

I appreciate the help with this, but for some reason it's still not working.  From the looks of it, it should work.  Do you know what could be driving this error message?  Thanks.

[Greg_Deckler]

@cheid_4838 For whatever reason and to my knowledge undocumented, CURRENT seems to be some sort of reserved word, which is why it is always a good idea to prefix variable names with __ or _ or some other character. So just do that.