putting time intervals into bins

[Anonymous]

Hi Everyone

I have been struggling with a task for quite a while now without getting anywhere. I hope You can help me. 

I have unixtime and datetime to work with.

I have a table of customers that use my app. I want to know how many are active in 1 minutes interval of the day (or maybe 5/10/15)

Eg: 

Customer no	starttime	endtime
223	2017-07-06 00:01:41.0000000	2017-07-06 00:02:41.0000000
221	2017-07-06 01:33:41.0000000	2017-07-06 01:39:41.0000000
220	2017-07-06 01:10:41.0000000	2017-07-06 01:32:41.0000000
225	2017-07-06 05:32:41.0000000	2017-07-06 09:32:41.0000000
226	2017-07-06 06:32:41.0000000	2017-07-06 07:32:41.0000000
224	2017-07-06 06:32:41.0000000	2017-07-06 06:50:41.0000000

I would like to make a graph that has a bin per minute on a given date on the x-axis and then count no. of active users in that minute on the y-axis. This would enable me to see that in the interval 2017-07-06 00:00 to 2017-07-06 00:01 there were X active customers. 
So if a customer has used the app in two minutes, he must be counted in two buckets. Note that a given customer can appear multiple times on a given day.

Best Regards and thanks in advance, Emilie

[v-lili6-msft · Accepted solution]

hi @Anonymous 

For your case, you could try this way as below:

Step1:

You must define a bin date time dim table, you could refer to this formula:

DateTime bins Table = 
SELECTCOLUMNS( 
    GENERATE (
        CALENDAR ( MIN ( data[starttime] ), MAX ( data[endtime] ) ),
        GENERATESERIES (
            TIME ( 00, 0, 0 ),  --From
            TIME ( 23, 59, 0 ), --To
            TIME ( 0, 1, 0 )    --Every 1 mins
        )
    ),
    "Datetime", [Date]+[Value],"Date",[Date],"Time",[Value]
)

Step2:

Use this formula get the result measure

Measure = 
CALCULATE (
    COUNTA ( data[Customer no] ),
    FILTER (
        data,
        data[starttime] <= SELECTEDVALUE ( 'DateTime bins Table'[Datetime] )
            && SELECTEDVALUE ( 'DateTime bins Table'[Datetime] ) <= data[endtime]
    )
)

Here is sample pbix file, please try it.

and here is a similar post, you could refer to it.

Regards,

Lin

[Anonymous]

Thanks a million! This was exactly what I needed!