Forum Discussion

Rocky1001's avatar
Rocky1001
Frequent Visitor
3 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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/1447523

  • Rocky1001's avatar
    Rocky1001
    Frequent Visitor
    3 years ago

    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 CodeStore_IDActivity TimesStamp
    Area 1Store 101/07/2022 03:05:04
    Area 1Store 101/07/2022 03:05:08
    Area 1Store 101/07/2022 03:05:13
    Area 1Store 101/07/2022 03:07:14
    Area 1Store 101/07/2022 03:09:47
    Area 1Store 101/07/2022 03:14:50
    Area 1Store 101/07/2022 03:15:13
    Area 1Store 101/07/2022 03:16:06
    Area 1Store 201/07/2022 03:18:18
    Area 1Store 201/07/2022 03:18:50
    Area 1Store 201/07/2022 03:20:03
    Area 1Store 201/07/2022 03:20:57
    Area 1Store 201/07/2022 03:27:39
    Area 1Store 201/07/2022 03:28:11
    Area 1Store 201/07/2022 03:28:27
    Area 2Store 301/07/2022 03:29:21
    Area 2Store 301/07/2022 03:33:24
    Area 2Store 301/07/2022 04:12:31
    Area 2Store 301/07/2022 04:18:06
    Area 2Store 301/07/2022 04:20:41
    Area 2Store 301/07/2022 04:21:44
    Area 2Store 301/07/2022 04:24:05
    Area 2Store 301/07/2022 04:25:59
    Area 2Store 401/07/2022 04:28:06
    Area 2Store 401/07/2022 04:29:08
    Area 2Store 401/07/2022 04:31:14
    Area 2Store 401/07/2022 04:33:10
    Area 2Store 401/07/2022 04:33:26
    Area 2Store 401/07/2022 04:34:11
    Area 2Store 401/07/2022 04:36:34

     

    Expected output

     

    Expected output
     Average time between events
    Store 195
    Store2101
    Store 3485
    Store 485
      
      
      
     Average time between events
    Area 1100
    Area 2288
      
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      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] )
      • Rocky1001's avatar
        Rocky1001
        Frequent Visitor
        3 years ago

        Hi, many thanks for this!

         

        I've tried this is my dataset of 1m rows and its maxing resources, any suggestions please?

         

        Thanks!