How to Subtract Dates in Power BI

Calculating the difference between two dates is a fundamental task in data analysis, allowing you to measure everything from project durations to customer lifetimes. This guide will walk you through several methods for subtracting dates in Power BI, starting with the simplest approach and moving to more flexible and powerful techniques.

Understanding Dates and DAX in Power BI

Before jumping into formulas, it's helpful to know how Power BI handles dates. Behind the scenes, Power BI stores dates and times as serial numbers. This is what allows you to perform mathematical operations on them, like subtraction. All the calculations we'll perform use DAX (Data Analysis Expressions), Power BI's formula language.

For any of these methods to work, make sure your date columns are correctly formatted. In the Power Query Editor or the Data view in Power BI, confirm your columns have a Date or Date/Time data type. An incorrect data type, like Text, will cause errors in your calculations.

Method 1: Simple Subtraction for Quick Result in Days

The most straightforward way to find the difference between two dates is to just subtract them. This method is incredibly quick and works perfectly when you only need the result in days.

Let's say you have a table of support tickets and you want to calculate how long each ticket was open. Your table has a StartDate and an EndDate column.

Step-by-Step Instructions

1. Navigate to the Data view by clicking the table icon on the left-hand pane in Power BI.
2. Select the table containing your dates.
3. In the "Table tools" ribbon at the top, click New Column. This will open the formula bar.
4. Enter the following DAX formula. Power BI's editor will help you autofill the column names as you type.

TicketDurationDays = YourTable[EndDate] - YourTable[StartDate]

Press Enter, and Power BI will calculate the difference for every row. The result will be a new column, TicketDurationDays, showing the elapsed time in days.

One thing to note: if you're using Date/Time columns, the result may include a decimal representing the fraction of a day. If you only want whole days, you can wrap your formula in the INT() function to remove the decimal portion.

TicketDurationDays = INT(YourTable[EndDate] - YourTable[StartDate])

Method 2: Use DATEDIFF for Ultimate Flexibility

While simple subtraction works for days, you'll often need the difference in other units like months, weeks, or even hours. For this, the DATEDIFF function is your best friend. It's the most robust and versatile method for date calculations in Power BI.

The syntax for DATEDIFF is:

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

• <start_date>: The earlier date.
• <end_date>: The later date.
• <interval>: The unit of time you want the result in. This can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, or SECOND.

Example: Calculating Project Duration in Months

Imagine you're managing projects and want to see the duration of each project in months. You have a table with ProjectStartDate and ProjectEndDate columns.

Following the same steps as before (Data view > New Column), use this DAX formula:

ProjectDurationMonths = DATEDIFF(Projects[ProjectStartDate], Projects[ProjectEndDate], MONTH)

Other Common Uses for DATEDIFF

• Employee Tenure in Years: Find how many years an employee has been with the company.

EmployeeTenureYears = DATEDIFF(Employees[HireDate], TODAY(), YEAR)

• Client Engagement in Weeks: Measure the time between a client's first and last purchase.

ClientEngagementWeeks = DATEDIFF(Clients[FirstPurchaseDate], Clients[LastPurchaseDate], WEEK)

• Support Ticket Response Time in Hours: Calculate the hours it took to first respond to a ticket.

ResponseTimeHours = DATEDIFF(Tickets[TicketCreatedTime], Tickets[FirstResponseTime], HOUR)

An Important Note on DATEDIFF

DATEDIFF calculates the number of interval boundaries crossed between two dates. This can sometimes produce results that seem counter-intuitive at first. For instance, DATEDIFF between December 31, 2023, and January 1, 2024, will return 1 YEAR, because it crossed the year boundary—even though it was only one day apart.

Advanced Scenario: Calculating Working Days (Excluding Weekends)

What if you only want to count business days? This is a common requirement for project planning and performance metrics. Subtraction and DATEDIFF both include weekends, so we need a more specific approach.

The easiest way to do this relies on a separate Calendar Table, which is a best practice in Power BI. A simple Calendar Table just needs a single column of all dates covering the span of your data.

For this example, let's assume you have a 'Dates' table with a column named [Date] and another column named [DayofWeek] where Monday=1 and Sunday=7.

Here’s how you can create a calculated column to find the number of working days (Mon-Fri) between two dates:

WorkingDays = 
CALCULATE(
    COUNTROWS('Dates'),
    DATESBETWEEN(
        'Dates'[Date],
        YourTable[StartDate],
        YourTable[EndDate]
    ),
    'Dates'[DayofWeek] < 6
)

Breaking Down the Formula:

• COUNTROWS('Dates'): Counts the number of rows in your 'Dates' table.
• DATESBETWEEN(...): Filters 'Dates' to only include dates between StartDate and EndDate of the current row.
• 'Dates'[DayofWeek] < 6: Counts only Monday through Friday.

This method gives you a precise count of business days, excluding weekends, for accurate timeline and performance analysis.

Displaying the Result in a "Years, Months, Days" Format

Sometimes you need to present date differences in a more human-readable format, like "2 years, 5 months, and 12 days." Because the length of months varies, there isn't one perfect DAX function for this. However, you can build a formula that provides a very good approximation.

Here is a DAX formula to create a formatted duration string. You add this as a new calculated column.

HumanReadableDuration = 
// Define the two dates we're working with
VAR StartDate = YourTable[StartDate]
VAR EndDate = YourTable[EndDate]

// Calculate the number of full years passed
VAR Years = DATEDIFF(StartDate, EndDate, YEAR)

// Calculate the date that is 'Years' number of years after our start date
VAR DateAfterYears = EDATE(StartDate, Years * 12)

// Find the number of full months between that new date and our end date
VAR Months = DATEDIFF(DateAfterYears, EndDate, MONTH)

// Calculate the date that is 'Months' after our 'DateAfterYears'
VAR DateAfterMonths = EDATE(DateAfterYears, Months)

// The remaining days are the difference between the EndDate and our final calculated date
VAR Days = DATEDIFF(DateAfterMonths, EndDate, DAY)

// Combine everything into a text string
RETURN
Years & " years, " & Months & " months, " & Days & " days"

This formula accurately steps through the years and months to determine the remaining days, providing a result that's much more accurate than simple division approximations.

Final Thoughts

From simple subtraction for days to the flexible DATEDIFF function and even advanced DAX for calculating working days, Power BI gives you all the tools you need for date calculations. The key is choosing the right method based on the units you need and whether you must account for non-working days.

Manually writing these DAX formulas is powerful, but it comes with a learning curve and can be time-consuming, especially when connecting multiple data sources. We built Graphed because we believe getting insights from your data shouldn’t feel like a chore. You can connect your marketing and sales data sources in seconds, and instead of writing formulas, you simply ask questions in plain English. For example, you could ask, "What was the average project duration in months for projects completed last quarter?" and get an instant dashboard - no complex DAX required.