Forum Discussion
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,
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
12 Replies
- fhill
Resident Rockstar
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
- fhill
Can you post a sample of your data? Also, what's the DAX error when you try to save this formula?
FOrrest
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.
What needs to changed to show all the 15-minute increments even if there's no data?
Thank you,
- fhill
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
