Forum Discussion
Grouping date/time stamp into 5 mins time interval
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
Resident RockstarThere'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
I know this is old, but i found this looking for a solution to my problem. Is there a way to modify this to work off a start_time and end_time, and to fill the 5 minute interval if it falls between a start and end time for a row entry?
To explain further, I have a cdr for a PBX, i am trying to create a 5 minute interval bar chart that shows if an agent was in a call or not during that 5 minute interval. Using the above, it only displays a bar for the 5 minute interval when the call started. I would like it to keep displaying a bar until it gets to the 5 minute interval after the call ended, and then display an empty bar for every 5 minute interval they were not in a call
Does that make sense?
Example of the data:
| Agent | Direction | start_stamp | end_stamp | duration |
| Steve | inbound | 11/10/2022 9:17 AM | 11/10/2022 10:17 AM | 3600 |