cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: putting time intervals into bins

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

Re: putting time intervals into bins

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: putting time intervals into bins

Thanks a million! This was exactly what I needed!

Announcements

New Ranks and Rank Icons in 2020

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,774)