filtering timebins

[Anonymous]

Hi 

So i have a massive dataset, that needs to be split up into bins. It is login times on an app. What i need is to know, in a 15 minutes interval, how many were online. However, i cannot get my measure to include people who have logged on after the bin has initiated.  I present an example below:

The most right column marks the the columns i would like to have counted. 

id_alias	startdatetime	Enddatetime	included in timebin 00:45:00-01:00:00
tIQIO11cj39nsJlhEicWnEpLP39D8dA4StvuhXV6	2019-09-17 23:03:35.000	2019-09-17 23:10:56.000
rb7qsDawG4PbI51JglwPPVH0wX8MqvcY7VeYDunH	2019-09-17 23:17:11.000	2019-09-18 03:53:04.000	x
0x5EqHJhrZ82KUSbx0WEk43uRZ68Hk1UfHv1srHD	2019-09-17 23:18:58.000	2019-09-17 23:26:18.000
rRclfwmlofrVNblvLIfeJOjTFyJa0Vbq10NOYa4I	2019-09-17 23:23:49.000	2019-09-18 01:24:52.000	x
0x5EqHJhrZ82KUSbx0WEk43uRZ68Hk1UfHv1srHD	2019-09-17 23:29:17.000	2019-09-17 23:57:33.000
1StjrU526aYV263XDPDLbLmwdYzAeQm5ldGTO9YV	2019-09-17 00:48:42.000	2019-09-18 03:46:43.000	x
1StjrU526aYV263XDPDLbL111YzAeQm5ldGTO9YV	2019-09-17 00:48:42.000	2019-09-18 00:57:43.000	x
1StjrU526aYV263XDPDLbL111YzAeQm5ldGTO9YV	2019-09-17 01:00:42.000	2019-09-18 03:46:43.000

Measure =

CALCULATE (

COUNTA ( 'Dataset'[id_alias] );

FILTER (

'Dataset';

'Dataset'[startdatetime] <= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] )

&& 'Dataset'[Enddatetime] >= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] )

|| 'Dataset'[startdatetime] >= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] ) && 'Dataset'[startdatetime] <= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] )+1

&& 'Dataset'[Enddatetime] >= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] )

)

)

(the +1 was an attempt to ensure 'smaller than the next bin'.)

The DateTime bins Table is made via the following:

DateTime bins Table =

SELECTCOLUMNS(

GENERATE (

CALENDAR ( MIN ( 'Forespørgsel1'[startdatetime] ); MAX ( 'Forespørgsel1'[Enddatetime] ) );

GENERATESERIES (

TIME ( 00; 0; 0 ); --From

TIME ( 23; 59; 0 ); --To

TIME ( 0; 15; 00 ) --Every 15 min

)

);

"Datetime"; [Date]+[Value];"Date";[Date];"Time";[Value]

)

I hope someone smarter than me can help with a solution. Thanks in advance!
Best Kirstine

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on my research, I created data to reproduce your scenario.

DateTime bins Table:

DateTime bins Table = 
SELECTCOLUMNS(
    GENERATE(
        CALENDAR(MIN('Table'[enddatetime]),MAX('Table'[enddatetime])),
        GENERATESERIES(
            TIME(00,0,0),
            TIME(23,59,0),
            TIME(0,15,00)
        )
    ),
    "Datetime",[Date]+[Value],"Date",[Date],"Time",[Value]
)

Table:

Then you may create two measures as follows.

IsCount = 
var _currentmax = SELECTEDVALUE('DateTime bins Table'[Datetime])
var _currentmin = SELECTEDVALUE('DateTime bins Table'[Datetime])-1/24/4

var _currentstart = MAX('Table'[startdatetime])
var _currentend = MAX('Table'[enddatetime])
return
IF(
    NOT(
        OR(_currentstart>_currentmax,_currentend<_currentmin)
    ),
    1,0
)

CountNumber = 
IF(
    HASONEVALUE('DateTime bins Table'[Datetime]),
    COUNTROWS(
        FILTER(
            'Table',
            [IsCount] = 1
        )
    ),
    0
)

Finally you can use the 'DateTime' column to as a slicer to filter how many 'id_details' between the corresponding datetime and 15 minutes before the datetime.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[Anonymous]

Thanks a million for your time! This was perfect!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on my research, I created data to reproduce your scenario.

DateTime bins Table:

DateTime bins Table = 
SELECTCOLUMNS(
    GENERATE(
        CALENDAR(MIN('Table'[enddatetime]),MAX('Table'[enddatetime])),
        GENERATESERIES(
            TIME(00,0,0),
            TIME(23,59,0),
            TIME(0,15,00)
        )
    ),
    "Datetime",[Date]+[Value],"Date",[Date],"Time",[Value]
)

Table:

Then you may create two measures as follows.

IsCount = 
var _currentmax = SELECTEDVALUE('DateTime bins Table'[Datetime])
var _currentmin = SELECTEDVALUE('DateTime bins Table'[Datetime])-1/24/4

var _currentstart = MAX('Table'[startdatetime])
var _currentend = MAX('Table'[enddatetime])
return
IF(
    NOT(
        OR(_currentstart>_currentmax,_currentend<_currentmin)
    ),
    1,0
)

CountNumber = 
IF(
    HASONEVALUE('DateTime bins Table'[Datetime]),
    COUNTROWS(
        FILTER(
            'Table',
            [IsCount] = 1
        )
    ),
    0
)

Finally you can use the 'DateTime' column to as a slicer to filter how many 'id_details' between the corresponding datetime and 15 minutes before the datetime.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan!

Thanks so much for your time! I really appreciate it! 

I think that i have not been clear enough on how i expect the result to look like: 

I would like a table like this: 

Date	Timebin	Number of customers
17/09/2019	00:00:00	x
17/09/2019	00:15:00	x
17/09/2019	00:30:00	x
17/09/2019	00:45:00	x
...
30/09/2019	23:45:00	x

Thanks again!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may create a table visual with the Datetime column from DateTime ins Table and drag the 'CountNumber' measure into the visual.

Here is the result. You can also use the highlight filter to see the detailed result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.