Forum Discussion

eb1362's avatar
eb1362
Regular Visitor
2 years ago

How to group time intervals

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.

 

2 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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