How to Show Duration in Power BI

Working with time-based data in Power BI can feel surprisingly tricky, especially when you need to show duration instead of a specific time of day. Power BI is great with dates and times, but calculating and displaying the total time between a start and end point - like a support ticket’s age or the length of a phone call - requires a specific approach. This tutorial will walk you through a few reliable methods for handling and visualizing duration in your reports, from simple calculations to polished, user-friendly formats.

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 Durations Are a Special Case in Power BI

The core of the issue is that Power BI's built-in Time data type is designed for a point in time (e.g., 9:15 AM), not a length of time (e.g., 9 hours and 15 minutes). When you subtract a start time from an end time, Power BI often returns a decimal number representing the fraction of a day. If you try to format this result as "Time," you might get a weird timestamp instead of the duration you expected.

For example, if a task takes 6 hours, the raw result of your calculation might be 0.25 (since 6 hours is one-quarter of a 24-hour day). If you format that as a time, Power BI might show it as 6:00 AM, which is not what you want. The key is to take control of the calculation and formatting yourself. Let’s look at how to do that correctly.

Method 1: Calculate Duration as a Decimal Number

The simplest way to handle duration is to store it as a decimal number in a single, consistent unit like hours, minutes, or seconds. This method is fantastic for use in calculations and visualizations like bar charts because you're working with a straightforward number.

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-by-Step Instructions

Imagine you have a table called 'SupportTickets' with a [TicketOpened] and [TicketClosed] column, both in DateTime format.

1. Create a Calculated Column

First, create a new calculated column in your table. You can do this from the 'Table view' in Power BI Desktop.

2. Calculate the Difference and Convert to Your Desired Unit

Write a DAX formula to subtract the start time from the end time and then multiply the result to convert it from days (the default unit) to hours, minutes, or seconds.

• To get duration in hours:

Duration in Hours = (SupportTickets[TicketClosed] - SupportTickets[TicketOpened]) * 24

• To get duration in minutes:

Duration in Minutes = (SupportTickets[TicketClosed] - SupportTickets[TicketOpened]) * 24 * 60

• To get duration in seconds:

Duration in Seconds = (SupportTickets[TicketClosed] - SupportTickets[TicketOpened]) * 24 * 60 * 60

After creating the column, make sure to set its data type to ‘Decimal Number’ in the Column tools tab.

Pros and Cons of This Method

• Pros: This approach is easy to implement and perfect for performing aggregations. You can easily find the AVERAGE, SUM, or MAX duration because the values are numeric. They also work perfectly on chart axes.
• Cons: Displaying 8.75 hours isn't nearly as readable as "8h 45m". This format isn’t ideal for tables, cards, or KPIs where human-friendly display is important.

Method 2: Format Duration into a DD:HH:MM:SS Text String

When readability is your top priority, especially in tables or KPIs, you’ll want to format your duration into a familiar days:hours:minutes:seconds format. This method converts the numeric duration into a text string that is easy for anyone to understand at a glance. Because the output is text, this is purely for display and cannot be used for mathematical operations.

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

Step-by-Step Instructions using a DAX Measure

Let's calculate the average ticket resolution time and format it. Creating this as a measure is more flexible than a calculated column, as it will react to filters in your report.

1. Calculate Total Duration in Seconds

It's easiest to work with a base unit of seconds. The DATEDIFF function is perfect here. First, let's create a very simple base measure for the average duration in seconds.

Avg Duration in Seconds = 
AVERAGEX(
    SupportTickets,
    DATEDIFF(SupportTickets[TicketOpened], SupportTickets[TicketClosed], SECOND)
)

2. Build the Formatted Measure

Now, create a new measure that uses the first one to build the formatted string. Using variables (VAR) in DAX makes this formula much cleaner and easier to read.

Average Duration Formatted = 
VAR TotalSeconds = [Avg Duration in Seconds]
VAR Days = INT(TotalSeconds / 86400) -- 86400 seconds in a day
VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)  -- Remaining hours
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)   -- Remaining minutes
VAR Seconds = MOD(TotalSeconds, 60)                -- Remaining seconds

RETURN
-- Use FORMAT to ensure two digits (e.g., 01, 02) for HH, MM, SS
FORMAT(Days, "0") & "d " & 
FORMAT(Hours, "00") & "h " & 
FORMAT(Minutes, "00") & "m " & 
FORMAT(Seconds, "00") & "s"

You can adjust the final RETURN statement to get the format you want. For an HH:MM:SS format, you could do this:

RETURN 
FORMAT(Hours, "00") & ":" & 
FORMAT(Minutes, "00") & ":" & 
FORMAT(Seconds, "00")

Pros and Cons of This Method

• Pros: Produces a highly readable output like "1d 04h 32m 15s". It's ideal for display purposes in tables, matrices, and cards.
• Cons: The output is a string (text). You cannot sum, average, or plot this value on a chart axis. Attempting to sort a column with this format will result in alphabetical sorting (e.g., "15:00:00" comes before "2:00:00"), which is not what you want.

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

Method 3: The Hybrid Approach (The Best of Both)

So how do you get the best of both worlds? The ideal solution is to have both a numeric value for calculations and sorting, and a formatted string for display. This hybrid approach uses two separate measures: one for the raw calculation and another for the formatting.

Let’s expand on what we started in Method 2.

1. Base Numeric Measure: Have a measure that calculates the duration as a number. We already have [Avg Duration in Seconds], but you could also use minutes or hours. Let’s create an hours-based one for charting.

_Avg Duration in Hours = 
AVERAGEX(
    SupportTickets,
    DATEDIFF(SupportTickets[TicketOpened], SupportTickets[TicketClosed], SECOND)
) / 3600.0

Tip: Some developers add an underscore (_) prefix to "helper" measures to signal to other report builders that this measure is primarily used as an input for other measures.

1. Formatting Measure: Create your user-facing formatted measure that refers to the base measure. We already have this with our [Average Duration Formatted] measure.

How to Use This Hybrid Approach in Visuals

This is where the magic happens. You gain complete flexibility depending on the visual you're using:

• For a Card visual needing a clear label: Use the [Average Duration Formatted] measure.
• For a Table: Put the [Average Duration Formatted] measure into the table so it’s easy to read. To sort the table correctly, drag the [_Avg Duration in Hours] measure into it as well, use it to sort the table ascending or descending, and then hide that numeric column by making it as narrow as possible or turning off text wrapping.
• For a Column Chart: Use [_Avg Duration in Hours] on the value axis to compare average duration across different support agents or ticket types. This works because it's a number.
• Improve Chart Tooltips: Here's a great trick. Use the numeric _Avg Duration in Hours in your chart, then drag the Average Duration Formatted measure into the "Tooltips" field of the visual. Now, when a user hovers over a bar showing a value like 4.5, the tooltip will display the clean, formatted "04h 30m 00s".

This hybrid technique offers the perfect balance of analytical power and presentation polish, making your reports both insightful and incredibly easy to understand.

Final Thoughts

Mastering duration calculations in Power BI is all about choosing the right approach for your needs. Whether you use simple numeric conversions for calculations, formatted DAX strings for clear presentation, or the flexible hybrid method, you now have the tools to handle any time-based analysis thrown your way.

We know that getting these DAX calculations just right can take hours of wrangling and debugging. This is exactly why we built Graphed. Instead of writing complex formulas to correctly format a duration, you can simply connect your data sources and ask questions in plain English like, "show me my average ticket resolution time this month by agent." We automatically generate the live dashboards and reports for you, including correctly handling tricky fields like durations, so you can spend less time fighting with DAX and more time finding insights.