topic Re: How to group time intervals in DAX Commands and Tips

How to group time intervals

eb1362 — Mon, 22 Apr 2024 16:34:16 GMT

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.

Re: How to group time intervals

lbendlin — Tue, 23 Apr 2024 00:11:21 GMT

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

Re: How to group time intervals

aduguid — Tue, 23 Apr 2024 05:09:31 GMT

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