How to Sum Duration in Power BI

Summing up time or duration in Power BI can be surprisingly tricky. You might expect to just drop a duration column into a visual and see the total, but Power BI often interprets it as a time of day, leading to nonsensical results above 24 hours. This article breaks down exactly how to handle and correctly sum duration values in Power BI using a straightforward DAX approach.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Is Summing Time in Power BI a Challenge?

The core of the problem lies in data types. Power BI doesn't have a specific "Duration" data type. When you have a column with values like "02:30:15" (representing 2 hours, 30 minutes, and 15 seconds), Power BI typically treats it as a "Time" data type, which is meant to represent a point in time - a specific time of day.

When you try to sum a "Time" column, Power BI gets confused. It attempts to add up these points in time, and if the total exceeds 24 hours, the result often rolls over. For instance, summing 14 hours and 16 hours might result in "10:00:00 AM" (14 + 16 = 30 hours, which is 1 day and 6 hours) instead of the "30:00:00" you need. This is useless for tracking things like total project hours, cumulative call durations, or total time spent on support tickets.

To solve this, we need to bypass Power BI's default time logic. The trick is to convert the duration into a plain number (like total seconds or minutes), sum that number, and then format the result back into a readable time format.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Get Your Data Ready

First, let's assume you have a data table containing duration information. This might be a duration column that's already calculated, or you might have Start Time and End Time columns. If you have start and end times, you can easily create a duration column in either Power Query or DAX.

In the Power Query Editor, you can select the End Time column, then the Start Time column (in that order), go to the "Add Column" tab, click "Time," and choose "Subtract."

Alternatively, you could create a calculated column in DAX:

Duration = 'YourTable'[EndTime] - 'YourTable'[StartTime]

For this tutorial, let's work with a simple table named 'ProjectTasks' that already has a 'Duration' column formatted as a Time data type.

Step 2: Convert Duration to Total Seconds (or Minutes)

This is the essential step where we convert the duration into a consistent numerical unit that can be easily summed. Seconds are often the most accurate unit to use, as it avoids issues with partial minutes. We will create a new calculated column in our table to hold this value.

1. Navigate to the Data view in Power BI and select the table containing your duration data.
2. Click on New column from the "Table tools" ribbon.
3. Enter the following DAX formula to calculate the total number of seconds for each row:

`Total Seconds = HOUR([Duration]) * 3600

• MINUTE([Duration]) * 60
• SECOND([Duration])`

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Understanding the DAX Formula:

• HOUR([Duration]): Extracts the hour part from the duration column (e.g., from "02:30:15" it gets 2). We multiply it by 3600 (60 minutes * 60 seconds) to get the number of seconds in those hours.
• MINUTE([Duration]): Extracts the minute part (e.g., 30). We multiply it by 60 to convert it into seconds.
• SECOND([Duration]): Extracts the second part (e.g., 15).

By adding these three parts together, you get the absolute total of seconds for each task. Your table should now include a 'Total Seconds' column full of whole numbers, ready for aggregation.

Step 3: Create a DAX Measure to Sum the Seconds

While you could just drag the 'Total Seconds' column into a visual, it's a best practice in Power BI to use measures for aggregations. Measures are more flexible and performant, especially in more complex models.

1. Right-click on your table in the Fields pane and select New measure.
2. Enter this simple DAX formula:

Sum of Total Seconds = SUM('ProjectTasks'[Total Seconds])

If you add this measure to a Card visual, you'll see a large number representing the total seconds for all tasks. This sum is correct, but it's not very human-friendly. The next step is to format it.

Step 4: Format the Sum into a DD:HH:MM:SS String

Now we need a second DAX measure that takes our [Sum of Total Seconds] and converts it back into a readable format like Hours:Minutes:Seconds. This measure will be responsible for the final presentation of our total duration.

Create another New measure and use the following DAX formula. This formula is a bit longer, but it's a very common and powerful pattern for this exact task:

`Formatted Total Duration = VAR TotalSeconds = [Sum of Total Seconds] VAR Days = INT(TotalSeconds / 86400) VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600) VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60) VAR Seconds = MOD(TotalSeconds, 60)

RETURN // This checks if there is at least one full day to display IF( Days > 0, Days & "d " & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00"), FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00") )`

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Decoding this Formatting Measure:

• VAR...: We use variables (VAR) to make the code cleaner and more efficient. TotalSeconds simply stores the result of our first measure.
• VAR Days: Calculates the number of full days by dividing the total seconds by 86,400 (the seconds in a day) and taking the integer part with INT().
• VAR Hours: We use the MOD() function here. MOD(TotalSeconds, 86400) gives us the leftover seconds after accounting for the full days. We then divide that remainder by 3600 to get the hours.
• VAR Minutes: Similarly, MOD(TotalSeconds, 3600) gives us the leftover seconds after accounting for full hours, which we then divide by 60.
• VAR Seconds: MOD(TotalSeconds, 60) gives us the final remaining seconds.
• RETURN: The RETURN statement constructs the final text string.

Now, drag your new Formatted Total Duration measure into a Card or Table visual. You'll see your summed duration displayed perfectly, even if it's thousands of hours long.

Final Thoughts

Working with time durations in Power BI simply requires a change in perspective. By converting time into a simple number for calculations and then back into a text format for display, you can overcome Power BI's default behavior and accurately report on cumulative time. The key is to calculate total seconds, sum them using a measure, and then use another formatting measure to present the result in a readable way.

Learning an entire language like DAX is powerful, but it can be time-consuming, especially when you just need quick answers about your business. We created Graphed to solve this by eliminating the steep learning curve. Instead of writing complex formulas manually, you can connect your data sources and simply ask questions in plain English, like "what was the total time spent on Project Apollo last month?" and Graphed creates the reports and dashboards for you automatically, so you can focus on insights instead of syntax.