How to Subtract Time in Power BI

Calculating the difference between two time points is a fundamental task in data analysis, whether you're tracking project timelines or customer support resolutions. In Power BI, this is handled using DAX (Data Analysis Expressions). This guide will walk you through exactly how to subtract time in Power BI, using clear examples and practical tips.

Why Do You Need to Subtract Time in Power BI?

Before diving into the formulas, it’s helpful to understand the common scenarios where calculating time differences is useful. Most business reporting relies on measuring duration to understand efficiency, performance, and timelines.

Here are a few relatable examples:

• E-commerce: Calculating the average shipping time by subtracting the Order Date from the Shipping Date.
• Project Management: Figuring out the duration of a project by finding the difference between the Start Date and End Date.
• Customer Support: Measuring ticket resolution time by subtracting the Ticket Created timestamp from the Ticket Closed timestamp.
• Marketing: Analyzing the time it takes for a lead to move from one stage to the next in your funnel, like from MQL Date to SQL Date.

In all these cases, you have a starting point and an ending point. Your goal is to find the duration between them in a specific unit, like days, hours, or minutes.

The Easiest Way: Using the DATEDIFF Function

The star of the show for time calculations in Power BI is the DATEDIFF function. It’s a powerful and flexible DAX function designed specifically for this purpose. Forget trying to subtract one date column from another like you might in Excel, DATEDIFF gives you far more control.

The basic structure (or syntax) for the function looks like this:

DATEDIFF(<start_date>, <end_date>, <interval>)

Let's quickly break down its three parts:

• **<start_date>**: This is your starting point. It's the earlier date/time in the period you're measuring (e.g., [OrderDate]).
• **<end_date>**: This is your ending point. It's the later date/time (e.g., [ShipDate]).
• **<interval>**: This tells Power BI which unit of time you want the result in. You can choose from several options.

Understanding the <interval> Options a Bit Better

The third part of the DATEDIFF function is what makes it so useful. You can get the result in almost any unit you need just by changing the interval.

Here are the common intervals you’ll use:

• SECOND: Total duration in seconds.
• MINUTE: Total duration in minutes.
• HOUR: Total duration in hours.
• DAY: Total duration in days.
• WEEK: Total duration in weeks.
• MONTH: Total duration in months.
• QUARTER: Total duration in quarters.
• YEAR: Total duration in years.

Practical Examples: Calculating Time Duration Step-by-Step

The best way to learn is by doing. Let’s walk through a few common scenarios and build the DAX formulas together. For these examples, we’ll assume you’ve already loaded your data into Power BI.

These calculations are typically done by creating a New Column, which performs the calculation for each row in your table and stores the result.

Example 1: Calculating Lead Time in Days

Let's say you have a table named SalesforceOpportunities that contains a [CreatedDate] and a [ClosedDate] for each sales opportunity. You want to create a new column called "Lead Time" that shows how many days it took to close each deal.

1. With your table visible in the 'Data' view of Power BI, click on New Column in the ribbon at the top.
2. A formula bar will appear. Type in the following DAX formula:

Lead Time (Days) = DATEDIFF(SalesforceOpportunities[CreatedDate], SalesforceOpportunities[ClosedDate], DAY)

Hit Enter, and Power BI will create a new column calculating the exact number of days between the creation and closing dates for every single row in your table. It’s that simple.

Example 2: Calculating Help Desk Ticket Duration in Hours

Now, let's work with time. Imagine you have a SupportTickets table with timestamps: [TicketOpenedAt] and [TicketClosedAt]. Since these are smaller timeframes, getting the duration in days isn't very helpful. Let's calculate it in hours and minutes.

Duration in Hours

1. Create another New Column.
2. Enter this formula to get the total duration in hours:

Resolution Time (Hours) = DATEDIFF(SupportTickets[TicketOpenedAt], SupportTickets[TicketClosedAt], HOUR)

This will give you the total number of hours it took to resolve the ticket.

Duration in Minutes

If you need even more detail, you can use the MINUTE interval.

Resolution Time (Minutes) = DATEDIFF(SupportTickets[TicketOpenedAt], SupportTickets[TicketClosedAt], MINUTE)

Example 3: Creating a "DD:HH:MM" Duration Format

Sometimes, just returning a single number like "75" hours isn't very intuitive. Your team might prefer seeing durations formatted like "3 days, 3 hours, 0 minutes." You can't do this with a single DATEDIFF function, but you can build this formatted text by combining a few DAX formulas.

This is a more advanced technique, but it's incredibly useful for building easy-to-read reports. Let's find the duration between a [StartDate] and an [EndDate].

Step 1: Get the Total Duration in Minutes

First, create a column to get the total difference in minutes. This gives us a base number to work with.

Total Minutes = DATEDIFF('YourTable'[StartDate], 'YourTable'[EndDate], MINUTE)

Step 2: Calculate the Days, Hours, and Minutes Separately

Now, using our [Total Minutes] column, we can create three new calculated columns to break this number down.

• Calculate the Days part: The number of minutes in a day is 1440 (24 hours * 60 minutes). We can use INT (integer) to get the whole number of days.

Days_Part = INT([Total Minutes] / 1440)

• Calculate the Hours part: We use the MOD function to find the remainder of minutes after accounting for the full days. Then we divide by 60 to find the hours.

Hours_Part = INT(MOD([Total Minutes], 1440) / 60)

• Calculate the Minutes part: Again, we use MOD to find the remainder of minutes left over after accounting for the hours.

Minutes_Part = MOD([Total Minutes], 60)

Step 3: Combine Everything into a Formatted String

Finally, create one last column to combine these parts into a clean-looking text format. We use the & symbol to join text and numbers together.

Formatted Duration = [Days_Part] & "d " & [Hours_Part] & "h " & [Minutes_Part] & "m"

Now you'll have a column that displays durations like "5d 14h 32m", which is much more presentable on a dashboard!

Important Tips and Common Pitfalls

As you work with time calculations in Power BI, you might run into a few common issues. Here’s what to look out for.

1. Data Types are Critical

DATEDIFF will only work if your start and end columns are formatted as a Date, Time, or DateTime data type. If your dates are stored as text (e.g., "01/01/2024"), it will throw an error. You can fix this in the Power Query Editor by selecting the column and changing its data type before you start writing DAX.

2. Be Careful with How a Boundary is Counted

This is a subtle but important detail: DATEDIFF counts the number of interval boundaries you cross between the start and end dates. For example, calculating the difference between December 31, 2023 and January 1, 2024 using the YEAR interval will return 1 because you crossed the boundary from 2023 to 2024. However, using the DAY interval would also return 1. Context matters, so always use the most granular interval that makes sense for your analysis (usually Days, Hours, or Minutes).

3. Handling Blanks or Still-Open Timelines

What happens if a project isn't finished yet or a support ticket is still open? Your [EndDate] or [TicketClosedAt] column might be blank (null). Running DATEDIFF on a blank value can cause errors or unexpected results.

You can use an IF statement paired with ISBLANK to gracefully handle this. For example, if the end date is blank, you could return another blank, zero, or even calculate the duration up to today's date using the TODAY() function.

// Return a blank if there's no end date
Duration = IF(
    ISBLANK('YourTable'[EndDate]), 
    BLANK(), 
    DATEDIFF('YourTable'[StartDate], 'YourTable'[EndDate], DAY)
)

Final Thoughts

Calculating time differences in Power BI boils down to understanding and using the DATEDIFF DAX function. By specifying a start date, end date, and the right interval, you can measure just about any duration your business needs to track. With a little more DAX, you can even format those durations to be perfectly clear and readable in your dashboards.

While mastering DAX is a powerful skill, the process of writing formulas, creating columns, and manually building visuals can be time-consuming, especially when you just need a quick answer. With our tool, you don't have to worry about functions like DATEDIFF or building reports click-by-click. We let you connect your data sources and simply ask in plain English, "What's our average ticket resolution time in hours this month?" and Graphed generates the live chart for you in seconds, saving you from all the manual steps.