Table of Contents
Power BI, a business analytics tool, allows users to round time values to the nearest quarter hour using its built-in functions. This can be achieved by using the ROUND function, which takes two arguments – the time value and the desired rounding increment. By specifying the rounding increment as 0.25, the time value will be rounded to the nearest quarter hour. This feature is useful for data analysis and reporting, as it allows for more accurate and consistent time calculations.
You can use the following syntax in DAX to round time to the nearest quarter hour:
Rounded Time = MROUND('my_data'[Datetime], TIME(0, 15, 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 quarter hour.’
For example, this formula would round the time 8:49:03 PM to 8:45:00 PM.
The following example shows how to use this syntax in practice.
Example: How to Round Time to Nearest Quarter 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 quarter 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(0, 15, 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 quarter hour:
For example:
- The time 10:15:00 AM has been rounded to 10:15:00 AM.
- The time 7:15:23 PM has been rounded to 7:15:00 PM.
- The time 1:15:09 AM has been rounded to 1:15:00 AM.
How This Formula Works
Recall the formula that we used to round each time to the nearest quarter hour:
Rounded Time = MROUND('my_data'[Datetime], TIME(0, 15, 0 )) + TIME(0, 0, 0)
This formula first uses the MROUND function to round the time in the Datetime column to the nearest 15 minutes, i.e. the nearest quarter 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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: