cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KirstineEmilie Frequent Visitor
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 nostarttimeendtime
223

2017-07-06 00:01:41.0000000

2017-07-06 00:02:41.0000000
2212017-07-06 01:33:41.00000002017-07-06 01:39:41.0000000
2202017-07-06 01:10:41.00000002017-07-06 01:32:41.0000000
2252017-07-06 05:32:41.00000002017-07-06 09:32:41.0000000
2262017-07-06 06:32:41.00000002017-07-06 07:32:41.0000000
2242017-07-06 06:32:41.00000002017-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
Community Support Team

Re: putting time intervals into bins

hi @KirstineEmilie 

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: putting time intervals into bins

hi @KirstineEmilie 

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.

View solution in original post

KirstineEmilie Frequent Visitor
Frequent Visitor

Re: putting time intervals into bins

Thanks a million! This was exactly what I needed!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

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