How to group time intervals

[eb1362]

Hello all,

My dataset has 1 column that I want to group time on the hour. The problem is, the time is on the hour, minutes, and seconds. In this column, I also have dates that represent student log in and the time. Is there a way to group the time where it accounts for on the hour log in versus multiple hour minute logins for Power BI? For example, instead of showing loggins for 12:00:50, 12:08:32, 12:04:51, I would like to look at anything between 12:00- 1:00 as 12:00.

[aduguid]

You could create a DAX query to generate a list of hours

Calendar Hours = 
VAR _start_time =   TIME(0, 0, 0)
VAR _end_time =     TIME(23, 0, 0)
VAR _duration =     TIME(1, 0, 0)

RETURN
GENERATESERIES(_start_time, _end_time, _duration)​

Then create a calculated column

Enrollments Completed Hour = 
IF(TIMEVALUE('YourTable'[Enrollments Completed On]) >= TIMEVALUE("23:30:00")
, TIMEVALUE("00:00:00")
, MROUND( TIMEVALUE('YourTable'[Enrollments Completed On]), TIME(01,00,00))
)

Then you could produce a chart using a measure like the following

Enrollments Count = CALCULATE(COUNT('YourTable'[Enrollments Completed On]) ) + 0

[lbendlin]

New column = TIME(HOUR([Old Column]),0,0)