Table Calculation: Difference between End Call and Start of Next

[pete234]

I am trying to do this in a power pivot table.
The table is large but not a lot of fields.
List of emp names and calls by date.
Row contains Name, Number called and Start Time and End Time per each call (I calculate this from Duration in Sec field).

What I need to do is calculate the time between (difference) for each End Time and the next Start Time so one row down.
This would also need to be contained within Day and Emp Name.

So like this:

Bob 5-29-2012 999-999-9999 11:20 AM 11:45 AM 0:25:00
Bob 5-29-2012 123-999-3333 1:20 PM 1:30 PM 0:10:00

So here the difference is 1:35:00. I'm thinking CALCULATE with some type of offset but not sure if it can work.

 

[pete234]

Laurent also I'm not sure if that is what I needed as I was looking for End time to next start time and your formula is start to start which does not account for duration on previous call.
The interest is the unoccupied time between calls.

 

[Laurent C]

Hi, I assumed calls do not overlap, so that the formula only uses the [Start time] column to determine when the next call for the same employee on the same day starts.

I left it up to you to calculate the time difference.

Do you use 32bit or 64bit? What is your datasource? Which version of PowerPivot do you use?

 

[pete234]

I did get a bit confused for a moment. I realized I needed to make the calculation after I posted that.
Now I imported a new call table with only 5k rows and no relationships in the workbook and it works.
Just not sure why my main workbook is giving up, it is only 20mb with 250k rows in 8 tables each with 2 relationships.
32 bit data is in other Excel workbooks, not ideal I'm sure.