PowerPivot - calculation issue with duplicate data and count of data

[vidurp]

Hello,

I am trying to calculate the difference in time between the current movement type and the last movement made by a particular vehicle according to the following data set in PowerPivot.

For instance, for Truck A I would like to calculate the time difference between movement 2 and 1 that occured on 3/11/2013 and 3/7/2013 respectively. I have developed the following DAX formula to current row with the transaction date of the next row, which I can then use to do a simple subtraction DAX formula to calculate time difference:

=IF(CALCULATE(MAX([TransactionDate]), FILTER(ALL('Data'), 'Data'[Vehicle]=EARLIER('Data'[Vehicle])), FILTER(ALL('Data'), 'Data'[LastMovementType]=EARLIER('Data'[MovementType])))<[TransactionDate],
BLANK(),
CALCULATE(MAX([TransactionDate]), FILTER(ALL('Data'), 'Data'[Vehicle]=EARLIER('Data'[Vehicle])), FILTER(ALL('Data'), 'Data'[LastMovementType]=EARLIER('Data'[MovementType]))))


The issue with the formula is that it doesn't allow me to use the VALUE/VALUES function instead of the MIN/MAX to populate the date. Instead, the MIN/MAX functions only pick the earliest or latest date that a MovementType occured on. When there are repetitions in LastMovementType, it picks the maximum date for the occurence of the particular LastMovementType. For instance, for Truck D's data on 1/31/2013, applying the above formula produces 4/23/2013 when I want it to produce 3/8/2013 which is the next day of the next movement. It produces 4/23/2013 because that is the maximum date where the LastMovementType is a 4.

I assume one method to solve this is by counting the number of repetitions of LastMovementType and ranking the number of duplicates to spit out the first repetition, or the second, or the third and so on. This would require a COUNT formula nested with an EARLIER function +1 (to continue down a rank).

Although this would work perfectly in VBA in a loop, I require some guidance on how to do this in a simpler way in PowerPivot. I would GREATLY appreciate any help you could provide as this has taken me a TON of time to solve up to this point. Any suggestions on optimizations would be appreciated!

Thanks!

-Vidur
[TABLE="width: 477"]
<TBODY>[TR]
[TD]Vehicle
[/TD]
[TD]TransactionDate
[/TD]
[TD]MovementType
[/TD]
[TD]LastMovementType
[/TD]
[/TR]
[TR]
[TD]Truck A
[/TD]
[TD="align: right"]3/7/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck A
[/TD]
[TD="align: right"]3/11/2013
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Truck A
[/TD]
[TD="align: right"]3/13/2013
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Truck A
[/TD]
[TD="align: right"]3/19/2013
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]1/8/2013
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]2/14/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]3/4/2013
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]3/27/2013
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]42
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]4/16/2013
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Truck B
[/TD]
[TD="align: right"]4/16/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]Truck C
[/TD]
[TD="align: right"]4/18/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck C
[/TD]
[TD="align: right"]4/30/2013
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Truck C
[/TD]
[TD="align: right"]1/3/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck C
[/TD]
[TD="align: right"]1/7/2013
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]1/8/2013
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]1/31/2013
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]43
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]3/8/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]3/12/2013
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]3/20/2013
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]3/21/2013
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]4/23/2013
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Truck D
[/TD]
[TD="align: right"]4/25/2013
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</TBODY>[/TABLE]