Forum Discussion
Rocky1001
3 years agoFrequent Visitor
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...
lbendlin
Super User
3 years agoI 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] )
Rocky1001
3 years agoFrequent Visitor
Hi, many thanks for this!
I've tried this is my dataset of 1m rows and its maxing resources, any suggestions please?
Thanks!
- lbendlin3 years ago
Super User
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.