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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emarc1
Advocate II
Advocate II

Round time DOWN to the nearest 5/15/30 minutes

I couldn't find anything on actually rounding time intervals down to the nearest X.

 

This is necessary to aggregate time data.

 

MROUND only rounds to the nearest specified multiple (so also rounds up).

 

eg:
MROUND( 00:07:00, 15) = 0
MROUND( 00:08:00, 15) = 15

 

Really we want any time between 00:00:00 and 00:14:59 to round down to 00:00:00, any time between 00:15:00 and 00:29:59 to round down to 00:15:00, etc.

1 ACCEPTED SOLUTION
jthomson
Solution Sage
Solution Sage

Would grouping the data into a new column using 15 minute bins not work for what you need to do?

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

To round down to nearest 15 mins, 30 mins, 1 hour, etc.

 

=FLOOR([Time],"0:15")

=FLOOR([Time],"0:30")

=FLOOR([Time],"1:00")

 

To round up, use CEILING(). Both work with any Date/Time value. They obviously work in a more conventional sense such as flooring/ceiling a number to the nearest 10, 100, 50, whatever....

 

=FLOOR(323,100) will be 300

=CEILING(59204,10) will be 59210

trinitykala
Frequent Visitor

You can still use MROUND() function.

 

=MROUND( [Time], "0:15" )   -- this will round up/down to 15 min.

 

Just remember that Mround will not recognise your column with time as time. So if you see a decimal number returned by MROUND(), don't panic 🙂 just change the data type to Time on your new column.

 

This is the formula that works:

 

ColRndTime = MROUND(Query1[ColTime],time(0,15,0))

Where: Data type = Time

And: Format = HH:mm

 

ColTime = Query1[READ_DATE]    

Where: Data type = Time

And: Format = HH:mm

 

READ_DATE = Data and Time of the source data

Where: Data type = Date / Time

And: Format = (G)

SteveCampbell
Memorable Member
Memorable Member

I'm not sure if you're talking about a time of day, or a measurement of time? Time is stored as an decimal between 0 and 1, so each 15 mins is equal to (1/24/60)*15, which is the same as 1/96.

 

Therefore:

 

FLOOR(VALUE([Time Val]), 1/96)

 

 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



@SteveCampbell In this case, I was trying to make a 'calendar' table for time of day.

 

I didn't know exactly how time was stored before. That might also explain why I've had issues with drag filling in excel.

If using for time of day, FLOOR(VALUE([Time Val]), 1/96) will work if you set to time and select the correct formatting. if it is a measurment of time, sorting it as time will be difficult after you get past 24 hours. Here it would be much better to just store as a value (such as seconds). 

Interestingly - date / time is just a number, with 0 being 12:00:00am on 12/30/1899.

Each day is a value of 1, and time is just the decimal of how much through the day we are. At 11/27/17 10:00:00am it is 43066.4166666,  so 43066 days after 12/30/1899, and .416666 through the current day



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Interesting way of doing it. I think bins is the simplest solution in Power BI though. I've stored the date as a separate column for my data so I can just group the data by time and filter by dates separtely.

emarc1
Advocate II
Advocate II

I'm going to attempt to answer my own question here...

FLOOR seems to work for this (I'd initially assumed it'd work like ROUND and work on the number of digits):

TIME( 
     HOUR( Time[Time] ),
     FLOOR( Time[Time], 5 ),
     0
)

This will round down to the 5 minute level.

 

 

I think this will work with 1, 2, 3, 4, 5, 10, 20, 15 or 30. I don't think it would work with odd intervals like 7 or 45 because it will reset on the next hour. For 45, you'd get 0 for everything between 00:00:00 and 00:44:59 and 45 for everything between 00:45:00 and 00:59:59.

 

Another alternative is to use SWITCH( TRUE(), [...]

 

eg for 5 minute intervals:

5 min = 
TIME( 
    HOUR( Time[Time] ),
    SWITCH(
	TRUE(),
        MINUTE( Time[Time] ) >= 55, 55,
	MINUTE( Time[Time] ) >= 50, 50,
        MINUTE( Time[Time] ) >= 45, 45,
	MINUTE( Time[Time] ) >= 40, 40,
        MINUTE( Time[Time] ) >= 35, 35,
	MINUTE( Time[Time] ) >= 30, 30,
	MINUTE( Time[Time] ) >= 25, 25,
	MINUTE( Time[Time] ) >= 20, 20,
        MINUTE( Time[Time] ) >= 15, 15,
	MINUTE( Time[Time] ) >= 10, 10,
        MINUTE( Time[Time] ) >= 5, 5,
	MINUTE( Time[Time] ) >= 0, 0
    ),
    0
)

 

Does anyone have any pointers here or is this about right?

 

Works brilliantly and the switch statement can be used in direct query calculated columns which is what I needed. Thank you.

Good solution - This worked best for me but I think you're missing the MINUTE() function in the top solution.

jthomson
Solution Sage
Solution Sage

Would grouping the data into a new column using 15 minute bins not work for what you need to do?

@jthomson I've not found bins have worked for my data before but I think in this case, you might be right. I had a feeling there'd be a simpler way.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.