Forum Discussion
Average time duration between events
Hi all, hoping someone can help please! Can you suggest how to caluclate the average time (will be mostly in seconds) between customer activity per Store, however I need to also be able to analys...
Hi, thanks for the tips, the data was sample so not sensitive but I've removed customerID as probably not needed anyhow.
Many thanks!
Sample data
| Area Code | Store_ID | Activity TimesStamp |
| Area 1 | Store 1 | 01/07/2022 03:05:04 |
| Area 1 | Store 1 | 01/07/2022 03:05:08 |
| Area 1 | Store 1 | 01/07/2022 03:05:13 |
| Area 1 | Store 1 | 01/07/2022 03:07:14 |
| Area 1 | Store 1 | 01/07/2022 03:09:47 |
| Area 1 | Store 1 | 01/07/2022 03:14:50 |
| Area 1 | Store 1 | 01/07/2022 03:15:13 |
| Area 1 | Store 1 | 01/07/2022 03:16:06 |
| Area 1 | Store 2 | 01/07/2022 03:18:18 |
| Area 1 | Store 2 | 01/07/2022 03:18:50 |
| Area 1 | Store 2 | 01/07/2022 03:20:03 |
| Area 1 | Store 2 | 01/07/2022 03:20:57 |
| Area 1 | Store 2 | 01/07/2022 03:27:39 |
| Area 1 | Store 2 | 01/07/2022 03:28:11 |
| Area 1 | Store 2 | 01/07/2022 03:28:27 |
| Area 2 | Store 3 | 01/07/2022 03:29:21 |
| Area 2 | Store 3 | 01/07/2022 03:33:24 |
| Area 2 | Store 3 | 01/07/2022 04:12:31 |
| Area 2 | Store 3 | 01/07/2022 04:18:06 |
| Area 2 | Store 3 | 01/07/2022 04:20:41 |
| Area 2 | Store 3 | 01/07/2022 04:21:44 |
| Area 2 | Store 3 | 01/07/2022 04:24:05 |
| Area 2 | Store 3 | 01/07/2022 04:25:59 |
| Area 2 | Store 4 | 01/07/2022 04:28:06 |
| Area 2 | Store 4 | 01/07/2022 04:29:08 |
| Area 2 | Store 4 | 01/07/2022 04:31:14 |
| Area 2 | Store 4 | 01/07/2022 04:33:10 |
| Area 2 | Store 4 | 01/07/2022 04:33:26 |
| Area 2 | Store 4 | 01/07/2022 04:34:11 |
| Area 2 | Store 4 | 01/07/2022 04:36:34 |
Expected output
| Expected output | |
| Average time between events | |
| Store 1 | 95 |
| Store2 | 101 |
| Store 3 | 485 |
| Store 4 | 85 |
| Average time between events | |
| Area 1 | 100 |
| Area 2 | 288 |
lbendlin
Super User
Super UserI think you may want to review your expected result by area.
Avg time between events =
VAR a =
SUMMARIZE (
'Table',
[Area Code],
'Table'[Store_ID],
[Activity TimesStamp],
"Prev",
VAR a = [Area Code]
VAR s = [Store_ID]
VAR t = [Activity TimesStamp]
RETURN
CALCULATE (
MAX ( 'Table'[Activity TimesStamp] ),
'Table'[Area Code] = a,
'Table'[Store_ID] = s,
'Table'[Activity TimesStamp] < t
)
)
VAR b =
ADDCOLUMNS (
a,
"diff",
IF ( ISBLANK ( [Prev] ), 0, ( [Activity TimesStamp] - [prev] ) * 86400 )
)
VAR c =
FILTER ( b, [diff] > 0 )
RETURN
AVERAGEX ( c, [diff] )
Hi, many thanks for this!
I've tried this is my dataset of 1m rows and its maxing resources, any suggestions please?
Thanks!
- lbendlin
You might have wanted to mention that earlier. In that case I would suggest you create an index column and use that instead of the timestamp filter.