Forum Discussion
putting time intervals into bins
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
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
2 Replies
v-lili6-msftCommunity Support
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