Measure for CountIF X hours from Time Stamp?

[pete234]

I made this measure to feed a daily pivot summary of call totals.
=IF(COUNT([Start Time])+.1666667>=30,TRUE)
It's obviously wrong but I don't know how to get it to be COUNTIF.
I want it to say true if it's 30 calls or more during 4 hours from Start time.
Right now it says TRUE if anything is >30 no matter what time.

I tried just making a calculation of Start Time +4 or .166667 and I keep getting an error saying it can't find Start Time and value can't be determined.

 

[NickyvV]

Pete,

Assuming [Start Time] is a column, it should also have a TableName in front of it, like: TableName[Start Time]
Could you tell us more on what tables / columns you have and where the measure will be used for?

 

[powerpivotpro]

Is Start Time a field you are going to put on the pivot, like on rows? Or is Start Time a fixed time like 00:00?

 

[pete234]

I figured out a way around this by getting them to accept calls before 1pm (lunch) for now which was easy enough.
But in the interest of "just in case" [Start Time] is fixed 00:00 from the output formatted in the daily log.
The table I'm working with feeds a pivot table but here I'm just using the raw data with a couple Related To fields.
The original idea was to not make a fixed "lunch time" but 4 hours from each reps first call, per day, how many calls?
I haven't tried to add the tablename but I think my formula is still wrong?
The fields are Rep Name, Start Time, Duration (Calc), Duration Secs, Date.
Table Name is CallData. But each day would need calc on MIN on of Start Time (not each start time) which to me is something like SUMX?

 

[powerpivotpro]

Can you send me the workbook, or a workbook with sample data illustrating the problem?

 

[pete234]

Can you send me the workbook, or a workbook with sample data illustrating the problem?

Data is work stuff so can't really send. I recreated it here to show you. It's just a call log really.
The formulas here really aren't relevant as that is done in Powerpivot on the table. Not sure how to turn off.
Column H would contain the Count of Calls that occurred before or equal to 4 hours after start. It's ok if it repeats for each rep each day. Thanks for any help.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Rep Team (Related To)	Rep Name	Date	Start Time	End Time (calc)	Duration Sec	Duration (calc)	Ct
2	North	Bob	10/1/2012	10:00 AM	10:02 AM	125	02:05	?
3	North	Bob	10/1/2012	10:15 AM	10:16 AM	100	01:40	*
4	North	Bob	10/1/2012	10:20 AM	10:20 AM	15	00:15	*
5	North	Bob	10/1/2012	11:02 AM	11:05 AM	200	03:20	*
6	North	Bob	10/1/2012	11:06 AM	11:08 AM	125	02:05	*
7	North	Bob	10/1/2012	11:15 AM	11:25 AM	630	10:30	*
8	North	Bob	10/1/2012	1:15 PM	1:20 PM	120	02:00	*
9	North	Bob	10/2/2012	9:00 AM	9:00 AM	25	00:25	*
10	North	Bob	10/2/2012	9:15 AM	9:15 AM	55	00:55	*
11	North	Bob	10/2/2012	10:20 AM	10:26 AM	400	06:40	*
12	North	Bob	10/2/2012	1:30 PM	1:34 PM	250	04:10	*
13	South	Barb	10/1/2012	9:00 AM	9:02 AM	145	02:25	*
14	South	Barb	10/1/2012	9:15 AM	9:17 AM	120	02:00	*
15	South	Barb	10/1/2012	10:20 AM	10:22 AM	150	02:30	*
16	South	Barb	10/1/2012	11:02 AM	11:05 AM	200	03:20	*
17	South	Barb	10/1/2012	11:14 AM	11:17 AM	220	03:40	*
18	South	Barb	10/1/2012	11:25 AM	11:30 AM	300	05:00	*
19	South	Barb	10/1/2012	11:45 AM	11:47 AM	150	02:30	*
20	South	Barb	10/1/2012	11:50 AM	11:52 AM	165	02:45	*
21	South	Barb	10/2/2012	11:14 AM	11:20 AM	400	06:40	*
22	South	Barb	10/2/2012	11:25 AM	11:26 AM	100	01:40	*
23	South	Barb	10/2/2012	11:45 AM	11:47 AM	140	02:20	*
24	South	Barb	10/2/2012	12:55 PM	12:58 PM	200	03:20	*
Sheet2