How to Calculate Time in Power BI

Calculating the time between two events is a fundamental part of business analysis. Whether you're tracking ticket resolution times, measuring employee efficiency, or analyzing customer call durations, you need a reliable way to work with time data. This guide walks you through the essential DAX functions and techniques for calculating time and durations in Power BI, turning confusing timestamps into clear, actionable metrics.

How Power BI Stores and Understands Time

Before diving into calculations, it's helpful to know how Power BI handles time. Power BI doesn't have a separate "Time" data type. Instead, it uses a DateTime data type that stores both date and time information together in a single field.

Behind the scenes, Power BI stores these values as decimal numbers. The integer part of the number represents the date (the number of days since a base date), and the fractional part represents the time of day as a fraction of a 24-hour period.

• 12:00 PM (midday) is stored as 0.5
• 6:00 AM is stored as 0.25
• 6:00 PM is stored as 0.75

You don't usually need to worry about these underlying decimal values, but knowing this exists helps explain some of the results you'll see later. For most calculations, you’ll work with DAX functions that interpret these numbers for you.

For your time calculations to be accurate, make sure your columns containing time information are set to the Date/Time or Date data type in the Power Query Editor or the Data view in Power BI.

The Easiest Way to Calculate Duration: Simple Subtraction

For the simplest duration calculations, you can often just subtract the start time from the end time. Let's imagine you have a table of support tickets with [StartTime] and [EndTime] columns formatted as DateTime.

To find the duration of each ticket, you can create a new calculated column in the Data view.

1. Navigate to the Data view and select your table.
2. Click "New column" from the table tools ribbon.
3. Enter the following DAX formula:

Ticket Duration = 'Tickets'[EndTime] - 'Tickets'[StartTime]

When you do this, Power BI will produce a result. If you format this new column as "Time," you'll often get exactly what you need if the duration is less than 24 hours. However, if you leave it as a general decimal, you might see something like 0.04167. This is where knowing about the underlying decimal system comes in handy. That decimal represents the fraction of a day. To convert it into more familiar units, you'd have to do some multiplication:

• To get Hours: ([EndTime] - [StartTime]) * 24
• To get Minutes: ([EndTime] - [StartTime]) * 24 * 60 or ([EndTime] - [StartTime]) * 1440
• To get Seconds: ([EndTime] - [StartTime]) * 24 * 60 * 60 or ([EndTime] - [StartTime]) * 86400

While this method works, it's not always the most intuitive or flexible. For more complex and readable calculations, the DATEDIFF function is usually a better choice.

The Powerhouse Tool: Calculating Durations with DATEDIFF

DATEDIFF is the go-to DAX function for finding the difference between two date/time values in whatever interval you need - be it seconds, minutes, hours, or days.

The syntax is straightforward:

DATEDIFF(<start_date_or_time>, <end_date_or_time>, <interval>)

• <start_date_or_time>: The starting point (e.g., your [StartTime] column).
• <end_date_or_time>: The ending point (e.g., your [EndTime] column).
• <interval>: The unit of time you want the result in. Your options are:

Example: Calculating Resolution Time in Hours

Let's use our support ticket data again. We want to find the resolution time for each ticket, measured in hours. We can create a calculated column with this formula:

Resolution Time (Hours) = DATEDIFF('Tickets'[StartTime], 'Tickets'[EndTime], HOUR)

This formula iterates through each row in the Tickets table, calculates the difference in hours between the StartTime and EndTime for that row, and stores it in the new "Resolution Time (Hours)" column. This gives you a clean, simple whole number representing the hours between the two events, making it easy to create summary visuals like an "Average Resolution Time" card.

Extracting Individual Time Components: HOUR, MINUTE, SECOND

Sometimes you don't need a duration, but rather a specific component of a timestamp. For example, you might want to analyze what time of day your website sees the most traffic or when support tickets are most frequently created.

DAX provides simple functions for this:

• HOUR(<datetime>): Returns the hour as a number from 0 (12:00 AM) to 23 (11:00 PM).
• MINUTE(<datetime>): Returns the minute as a number from 0 to 59.
• SECOND(<datetime>): Returns the second as a number from 0 to 59.

Example: Analyzing Peak Ticket Submission Times

Let's say you want to build a chart showing how many support tickets are created each hour of the day. First, you'll need to create a calculated column that extracts the hour from your [StartTime] column.

Creation Hour = HOUR('Tickets'[StartTime])

This will create a column containing a number from 0 to 23. You can now use this "Creation Hour" column as the axis in a bar chart and use a count of your ticket IDs as the value. This will instantly visualize your peak and off-peak support hours, helping you plan your staffing rota more effectively.

Neatly Formatting Time Durations for Reports

Calculations are one thing, but presenting them clearly is another. When you calculate a duration, like the average call time, you might get a result in raw seconds (e.g., 150) or decimal hours (2.5). Most of the time, you'll want to display this in a more readable format, like HH:MM:SS (e.g., 00:02:30).

The FORMAT function can help, but it requires a DateTime value to work with time formats properly. Simply handing it a number of seconds won't work. Instead, we need a clever way to convert our duration (often in seconds) back into a format that FORMAT can understand.

Step-by-Step: Creating a Formatted Average Time Measure

Let's say we've calculated the average resolution time in seconds using a measure like this:

Avg Resolution (Seconds) = AVERAGE(DATEDIFF('Tickets'[StartTime], 'Tickets'[EndTime], SECOND))

Now, let's create a new measure to format this nicely. This DAX formula looks more complex, but it's a very common and powerful pattern.

Avg Resolution Time Formatted = VAR TotalSeconds = [Avg Resolution (Seconds)] VAR Hours = INT ( TotalSeconds / 3600 ) VAR Minutes = INT ( MOD ( TotalSeconds, 3600 ) / 60 ) VAR Seconds = MOD ( TotalSeconds, 60 ) VAR TimeValue = TIME ( Hours, Minutes, Seconds ) RETURN FORMAT ( TimeValue, "hh:mm:ss" )

Let's Break That Down:

1. VAR TotalSeconds = ...: First, we store our base calculation (the average duration in seconds) in a variable called TotalSeconds. This makes the DAX cleaner and more efficient.
2. VAR Hours = ...: We calculate the number of whole hours by dividing the total seconds by 3600 (the number of seconds in an hour) and using INT to discard any remainder.
3. VAR Minutes = ...: We use the MOD function to get the remaining seconds after the hours are accounted for, then divide by 60 to find the whole minutes.
4. VAR Seconds = ...: We use MOD again to find the seconds remaining after minutes are accounted for.
5. VAR TimeValue = TIME(...): We use the TIME function to build a proper time value from our calculated hours, minutes, and seconds.
6. RETURN FORMAT(...): Finally, we use the FORMAT function to convert our newly created TimeValue into a text string in the hh:mm:ss format.

When you place this [Avg Resolution Time Formatted] measure into a Card visual, you get a clean, professionally formatted time display that is easy for anyone to read and understand.

Final Thoughts

Learning how to calculate and format time in Power BI is a critical skill for anyone working with performance or operational data. By mastering core DAX functions like DATEDIFF for duration, HOUR for trend analysis, and a formatting pattern for nice visuals, you can move beyond simple timestamps and deliver real insights about when and how long business activities are taking.

Wrestling with DAX patterns can definitely feel like a workout. At Graphed , we built a tool to skip the manual setup and complex formulas. We enable you to connect your data sources like Salesforce or HubSpot, and then simply ask in plain English, "What was our average ticket resolution time last month?" From there, we build the dashboard for you in seconds, handling all the calculations and formatting automatically so you can focus on the insights, not the setup.