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.

 

[JavierGuillen]

One way of doing this adding - as a calculated column in the PowerPivot window - the following expression (called [Prior End Time]):

CALCULATE( MAX( [end time] ), FILTER( Table1, [start time] < EARLIER(Table1[start time]) ) )

Then create a second calculated column as:

IF(NOT(ISBLANK([Prior End Time]) ), FORMAT([start time] - [Prior End Time], "H:mm") )

 

[pete234]

I get a "too few arguments" for FILTER when I used this in the calc window. It says I only have one arg and I need 2.
I think I see how this could work so maybe you could tell me how to fix. thanks

=CALCULATE(MAX([End Time]),FILTER(CallNew[Start Time]<EARLIER(CallNew[Start Time]))

I have closing parenthesis not sure if the forum is showing them.

 

[JavierGuillen]

The filter function should contain 2 parameters, the table that you will filter as well as the filter condition.

FILTER( Table1, [start time] < EARLIER(Table1[start time]) )


In the formula suggested, the filter condition is taking all the rows up until the start time of the current row (excluding it). out of all rows, it then selects the MAX end time available -

 

[pete234]

Javier,
I wanted to show you a small version of this table so I can more fully explain. The MAX needs to be limited to a single day per employee. The team and Week is a Related lookup to 2 tables. The table is called "CallNew". Do you think it can still work with this data? It would be very rare for only a single call on a day but it is possible.

Excel Workbook
	A	B	C	D	E	F	G
1	Emp Name	Date	Start Time	End Time	Duration	Week #	Team
2	Bob S	5/27/2012	9:00 AM	9:02 AM	2:00	20	North
3	Bob S	5/27/2012	9:15 AM	9:19 AM	4:00	20	North
4	Bob S	5/28/2012	9:22 AM	9:27 AM	5:00	20	North
5	Sam T	5/15/2012	9:10 AM	9:15 AM	5:00	19	South
6	Sam T	5/16/2012	10:20 AM	10:22 AM	2:00	19	South
7	Sam T	5/17/2012	11:20 AM	11:35 AM	15:00	19	South
8	Angie	5/27/2012	9:00 AM	9:15 AM	15:00	20	North
9	Angie	5/27/2012	10:00 AM	10:20 AM	20:00	20	North
10	Angie	5/27/2012	10:30 AM	10:40 AM	10:00	20	North
11	Angie	5/27/2012	1:00 PM	1:15 PM	15:00	20	North
12	Angie	5/28/2012	9:00 AM	9:14 AM	14:00	20	North
Sheet1

 

[JavierGuillen]

Sure.

Try these as two calculated columns:

first one:

[Prior End Time]
=IF(
CALCULATE( COUNTROWS( Table1 ),
FILTER( Table1, Table1[Date] = EARLIER( Table1[Date] ) &&
Table1[Emp Name] = EARLIER( Table1[Emp Name] )
)
) > 1,
CALCULATE( MAX(Table1[End Time] ) ,
FILTER( Table1, Table1[Start Time] < EARLIER( Table1[Start Time] ) &&
Table1[Emp Name] = EARLIER( Table1[Emp Name] ) )
)
)



second one:

[Lapse]
=IF(NOT(ISBLANK([Prior End Time]) ), FORMAT(Table1[start time] - [Prior End Time], "H:mm") )


for days in which there is only one call I would assume no 'lapse' time should be calculated

 

[Laurent C]

You can create a calculated column to get the starttime of the next call (same day, same employee) as follows:

=CALCULATE(
MIN([Start Time]) ;
ALLEXCEPT(Data;Data[Emp Name];Data[Date]) ;
Data[Start Time] > EARLIER([Start Time])
)

This formula will not return any value if the employee has not had any further calls on the same day. From there on, calculating the time intervall between two calls, should be easy.

 

[pete234]

Laurent, I get errors after each semi-colon that syntax for ";" is invalid.
I also tried comma but this didn't work. I have copied and pasted to my data.

Javier, your idea may have been working but it will not complete before cancelling itself due to memory limit.
I am working with a table of 75k rows and 12 columns if this matters.
I can try to delete some extra data and see if that helps.
I won't have this much data but in the future, just need to build out my totals now.

 

[Laurent C]

Sorry about semi-columns. I work with French locales and MS decided they should partially localize DAX expressions in v2.0

=CALCULATE(
MIN([Start Time]) ,
ALLEXCEPT(Data,Data[Emp Name],Data[Date]) ,
Data[Start Time] > EARLIER([Start Time])
)

The expression worked for me, after I used the data you provided.

The only thing I did before I imported it in PowerPivot was to name the table "Data".

What other errors do you get after you replaced the commas?

 

[pete234]

Laurent I think your formula was working too but I can't seem to do any more calculations without the "out of memory" failure.
I can't even delete out other calculated columns to try to optimize.
Really frustrating limit, as Excel is only using 250 mb of memory and I have 6 GB and machine says 50% used.