cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors