Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
eb1362
Regular Visitor

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.

eb1362_0-1713803636876.png

 

2 REPLIES 2
aduguid
Resolver III
Resolver III

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))
)

 

 

aduguid_0-1713844912129.png

 

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

 

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

 

aduguid_1-1713845020309.png

lbendlin
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.