Calculate unique sessions in 24 hrs rolling window for each transaction

[deedeedudu]

Hi all,

I have a qurious problem that i need help with. I have the following data:

datetime	contact_id
3/1/2022  5:01:09 AM	207736
3/1/2022  5:01:20AM	207736
3/2/2022  5:22:09 AM	207736
3/2/2022  5:23:09 AM	2012444
3/2/2022  5:24:09 AM	207222
3/2/2022  5:25:09 AM	207222
3/2/2022  5:26:09 AM	2177363
3/2/2022  5:27:09 AM	257736
3/2/2022  5:28:09 AM	27736

I want to consolidate the data for the entire month as following:

1. If multiple transactions are happening for same contact_id over a period of 24 hrs starting from the first transaction, then only the first should be counted in the session

2. Any transaction after the 24 hrs window post first transaction for that contact_id should be treated as a separate session

Finally i should get the consolidated data as below that has sesssion count included:

Sessions	datetime	contact_id
1	3/1/2022  5:01:09 AM	207736
2	3/2/2022  5:22:09 AM	207736
3	3/2/2022  5:23:09 AM	2012444
4	3/2/2022  5:24:09 AM	207222
5	3/2/2022  5:26:09 AM	2177363
6	3/2/2022  5:27:09 AM	257736
7	3/2/2022  5:28:09 AM	27736

[v-cgao-msft · Accepted solution]

Hi @deedeedudu,

What is your expected output? If it's a calculated table you can try these formulas.

Table.

datetime_2 =
VAR _cur_datetime = 'Table'[datetime]
VAR _min_datetime =
    CALCULATE (
        MIN ( 'Table'[datetime] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
        )
    )
VAR _datetime =
    DATEDIFF ( _min_datetime, _cur_datetime, SECOND ) / 3600
RETURN
    IF ( _datetime < 24, _min_datetime, _cur_datetime )

Table2.

Table 2 = 
SUMMARIZE('Table','Table'[contact_id],'Table'[datetime_2])

Sessions = RANKX('Table 2','Table 2'[datetime_2],,ASC)

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc7LCYAwEEXRVsKsA5l5k49mZwFWENKbtViZiIsEHHF/uNzWSIMEMHAeLlWWyqvbdvIELkUzdf8mYINgEMCuzEQnIogxGibOGQAGSf8kDyL3jBqmDJM+hpdBHtEv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [datetime = _t, contact_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type datetime}, {"contact_id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"contact_id"}, {{"Temp", each _, type table [datetime=nullable datetime, contact_id=nullable number, Index=number]}}),
    //Function Start
    fxProcess=(Tbl)=>
    let
        #"Added Custom" = Table.AddColumn(Tbl, "Custom", each Duration.TotalHours([datetime]-Tbl[datetime]{0})),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Retain", each if [Custom]=0 or [Custom]>24 then "Y" else "N"),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Retain] = "Y")),
        #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Retain"})
    in
        #"Removed Columns1",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"datetime"}, {"datetime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"datetime", "contact_id"})
in
    #"Reordered Columns"