Power BI: How to Round Time to the Nearest Hour


You can use the following syntax in DAX to round time to the nearest hour:

Rounded Time = MROUND('my_data'[Datetime], TIME(1, 0, 0 )) + TIME(0, 0, 0)

This particular example creates a new column named Rounded Time that rounds the time in the Datetime column of the table named my_data to the nearest hour.

The following example shows how to use this syntax in practice.

Example: How to Round Time to Nearest Hour in Power BI

Suppose we have the following table named my_data in Power BI that contains information about total sales made by some company at specific times:

Suppose that we would like to round each time in the Datetime column to the nearest hour.

To do so, click the Table tools tab, then click the icon called New column:

Then type the following formula into the formula bar:

Rounded Time = MROUND('my_data'[Datetime], TIME(1, 0, 0 )) + TIME(0, 0, 0)

This will create a new column named Rounded Time that contains the time from the Datetime column rounded to the nearest hour:

Power BI round time to nearest hour

For example:

  • The time 10:15:00 AM has been rounded to 10:00:00 AM.
  • The time 7:15:23 PM has been rounded to 7:00:00 PM.
  • The time 1:15:09 AM has been rounded to 1:00:00 AM.

And so on.

How This Formula Works

Recall the formula that we used to round each time to the nearest hour:

Rounded Time = MROUND('my_data'[Datetime], TIME(1, 0, 0 )) + TIME(0, 0, 0)

This formula first uses the MROUND function to round the time in the Datetime column to the nearest 1 hour.

Then, we add TIME(0, 0, 0) which adds zero time to the rounded time, but it ensures that the final result is shown as a time instead of a numeric value.

Note: You can find the complete documentation for the MROUND function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

Power BI: How to Extract Hour from Datetime
Power BI: How to Extract Date from Datetime
Power BI: How to Add Days to Date

Leave a Reply

Your email address will not be published. Required fields are marked *