cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deedeedudu
Helper I
Helper I

Calculate unique sessions in 24 hrs rolling window for each transaction

Hi all,

 

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

 

datetimecontact_id
3/1/2022  5:01:09 AM207736
3/1/2022  5:01:20AM207736
3/2/2022  5:22:09 AM207736
3/2/2022  5:23:09 AM2012444
3/2/2022  5:24:09 AM207222
3/2/2022  5:25:09 AM207222
3/2/2022  5:26:09 AM2177363
3/2/2022  5:27:09 AM257736
3/2/2022  5:28:09 AM27736

 

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:

 

Sessionsdatetimecontact_id
13/1/2022  5:01:09 AM207736
23/2/2022  5:22:09 AM207736
33/2/2022  5:23:09 AM2012444
43/2/2022  5:24:09 AM207222
53/2/2022  5:26:09 AM2177363
63/2/2022  5:27:09 AM257736
73/2/2022  5:28:09 AM27736
1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @deedeedudu,

 

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

Table.

vcgaomsft_0-1652685585259.png

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)

vcgaomsft_1-1652685689086.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @deedeedudu,

 

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

Table.

vcgaomsft_0-1652685585259.png

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)

vcgaomsft_1-1652685689086.png

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
Super User
Super User

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"

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors