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 analyse it by Area if required.
I would like to do this entirely in measures as opposed to caculated columns due to data volume.
(Customer Id is probably not required)
Many thanks!
5 Replies
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi, 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
I 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] )
