cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Grouping date/time stamp into 5 mins time interval

Hello,

 

I have a column of incidents that span over a month in date/time stamp format like 

 

11/21/2017 5:04:45 AM

11/21/2017 5:07:20 AM

11/21/2017 5:08:18 AM

11/21/2017 6:01:23 AM

.

.

.

 

 

The big picture is that I would like to know the number of incidents that occur within 5 min timeframe during the entire month. That is, with a 5 min interval troughout each day of the entire month, how many incidents in the column fall within the time interval.

 

 

How would I go about implementing that?

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

There's probably a more DAX / Programmtic way of doing this, but I tend to be very 'logic driven' in my work..  There's always more than 1 way to solve a problem....

 

1. User PowerBI to Duplciate your data into 2 new columns formatting them Date (To get Date Only) and Time (To get Time Only).

2. Create the following Custom Columns outside of Query Editor:

 

Minutes = DATEDIFF(DATE(1899,12,30),Table1[Time ONLY],MINUTE)                          // A Test run at producing Minutes only from a TIME formatted column.

5Min_Data = INT(DATEDIFF(DATE(1899,12,30),Table1[Time ONLY],MINUTE) / 5)        // INTEGER your minutes divided by 5 to give you whole numbers to group you data by...

5Min_Time = TIME( INT([5Min_Data]*5/60), (( ([5Min_Data]*5/60)-INT([5Min_Data]*5/60))*60)  ,0)     //  Nobody wants to see '11/21/2016 60' or 11/21/2016 180' - Use this logic to rebuild a TIME format in 5 minute intervals.

 

3. Create your visual with 'Date Only' and '5Min_Time' stacked on the Asix.  By defult this will give you incidents by day, but you can use the drill down feature to get this report in 5 minute intervals.  (** If you don't like the drill down, you can recombine Date Only & 5 Min Time into a merged column also.. )

 

Thank You,

FOrrest

 

Capture.PNGCapture2.PNG

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

10 REPLIES 10
Highlighted
Super User I
Super User I

There's probably a more DAX / Programmtic way of doing this, but I tend to be very 'logic driven' in my work..  There's always more than 1 way to solve a problem....

 

1. User PowerBI to Duplciate your data into 2 new columns formatting them Date (To get Date Only) and Time (To get Time Only).

2. Create the following Custom Columns outside of Query Editor:

 

Minutes = DATEDIFF(DATE(1899,12,30),Table1[Time ONLY],MINUTE)                          // A Test run at producing Minutes only from a TIME formatted column.

5Min_Data = INT(DATEDIFF(DATE(1899,12,30),Table1[Time ONLY],MINUTE) / 5)        // INTEGER your minutes divided by 5 to give you whole numbers to group you data by...

5Min_Time = TIME( INT([5Min_Data]*5/60), (( ([5Min_Data]*5/60)-INT([5Min_Data]*5/60))*60)  ,0)     //  Nobody wants to see '11/21/2016 60' or 11/21/2016 180' - Use this logic to rebuild a TIME format in 5 minute intervals.

 

3. Create your visual with 'Date Only' and '5Min_Time' stacked on the Asix.  By defult this will give you incidents by day, but you can use the drill down feature to get this report in 5 minute intervals.  (** If you don't like the drill down, you can recombine Date Only & 5 Min Time into a merged column also.. )

 

Thank You,

FOrrest

 

Capture.PNGCapture2.PNG

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

Highlighted

Hi @fhill 

 

Hoping you can help,I know its some time later. Im getting an error in my formula...

 

Minutes = INT(DATEDIFF(DATE(1899,12,30),RD[Start time],MINUTE) / 30)
 
Any reason for the red highlight ? 
Highlighted

Can you post a sample of your data?  Also, what's the DAX error when you try to save this formula?

 

FOrrest



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted

This was very useful!.  I know this has been posted awhile back but I need a little help.

 

I've changed the formula to show 15-minute increments. Data is displayed when there's data within the increment (ex. 10:00am -10:15am) but when there's no data it doesn't show the increment. 

 

Ex. There's no data between 10:15am-10:30am, it doesn't show if there's no data (blank) or a zero value.

 

imonline_0-1598463716608.png

 

What needs to changed to show all the 15-minute increments even if there's no data?

 

Thank you,

Highlighted

 

You'll likely need a DATE Table & TIME Table (in 15 Minute Increments) to compare against, if you want to return the blank periods inbetween the data available, the '15 Minute Times' from the Time Table will need to become your Axis and a COUNT of Linked Total Calls from your Data Table as the Values (or Sum).


FOrrest

 

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted

I'll try it and let you know if it works.

Highlighted

I've used your formula:

15Min_Data = INT(DATEDIFF(DATE(1899,12,30),Table1[Time ONLY],MINUTE) / 15) 

15Min_Time = TIME( INT([15Min_Data]*15/60), (( ([15Min_Data]*15/60)-INT([15Min_Data]*15/60))*60)  ,0) 

 

Put the "15Min_Time " in the axis with my (COUNT)TotalCalls as Value

I'm also using the Time Line visualization to pick the date I want.

 

Everything works good but it won't show any 15 min increment if there's no data.

Ex:  No data between 10:15am - 11:0 am

 

How can I get it to show increments with zero or no data?

 
Highlighted

The DAX in this thread was to 'Group' random times into 15 minute increments.  Once you do that (or if your data is already in 15 minute increments to begin with you can skip those steps...) you'll need to create a 'Time-Table' of ALL Time Options Possible.  (Hopefully you are dealing with 1 Day at a Time, and not Spanning Days?)

 

There's CODE ways to do this, but Excel helps.  I manually enter 00:00, 00:15, 00:30, then allow Excel to COPY DOWN all the way automatically to 23:45.   Then Copy / Paste 

fhill_0-1598537425211.png

 

 

Now Create this as your 'Time Table'.

fhill_1-1598537613555.png

 

Now join your 15 Minute 'Times' (In Time format) to your Time:

fhill_2-1598537670350.png

 

** NOW ** you can use the Time_Table as your Axis, and since the data is joined together, your Values as Counts to get the Gaps you are looking for...

 

fhill_3-1598537716775.png

 

FOrrest

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted

SOLVED!

Sorry it took awhile to get back.

This worked!

Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors