How to Use DATEDIFF in Power BI

Calculating the time between two dates is a fundamental task in data analysis, whether you're trying to figure out how long a customer has been with you, how overdue an invoice is, or the average time it takes to resolve a support ticket. In Power BI, the DATEDIFF function is your go-to tool for these scenarios. This article will walk you through how to use the DATEDIFF function in Power BI with practical examples to help you calculate durations and find time-based insights in your data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the DATEDIFF Function?

At its core, DATEDIFF is a DAX (Data Analysis Expressions) function that returns the number of specified time intervals - like days, months, or years - that have passed between two dates. Unlike simple date subtraction, which only gives you a number representing days, DATEDIFF offers the flexibility to measure time in a unit that makes the most sense for your analysis.

You can use DATEDIFF when creating a new calculated column in your data table or as part of a more complex measure in your Power BI reports.

Breaking Down the DATEDIFF Syntax

The syntax for the function looks like this:

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

Let's look at each of the three arguments you need to provide:

• <start_date>: This is the beginning of your time period. It must be a valid date expression, typically a reference to a date column in your table, like Orders[OrderDate].
• <end_date>: This is the end of your time period. Similar to the start date, it should be a valid date, like Orders[ShipDate].
• <interval>: This is where you specify the unit of time you want to measure the difference in. It's the most powerful part of the function.

Understanding DATEDIFF Intervals

You can choose from several interval options to get your result in the perfect format:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

Choosing the correct interval is key. For example, if you're analyzing shipping times, using DAY or HOUR would be appropriate. If you're calculating employee tenure, MONTH or YEAR would be a better fit.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Putting It Into Practice: DATEDIFF Examples

The best way to understand DATEDIFF is to see it in action. Let's walk through a few common business scenarios where this function becomes incredibly useful. For these examples, we'll assume you have a 'Sales' table with columns like OrderDate and ShipDate, and an 'HR' table with HireDate and LeaveDate.

Example 1: Calculating Shipping Times in Days

A frequent request is to find out how long it takes to ship an order after it has been placed. This helps identify bottlenecks in the fulfillment process.

Let's create a calculated column in our 'Sales' table called "Days to Ship."

1. Navigate to the Data view in Power BI and select your 'Sales' table.
2. Click on "New Column" in the toolbar.
3. In the formula bar, type the following DAX expression:

Days to Ship = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)

Once you press Enter, Power BI will add a new column to your table. For each row, this column will show the total number of days between the OrderDate and the ShipDate. Now you can easily visualize average shipping times or flag orders that took too long to fulfill.

Example 2: Calculating Employee Tenure

Understanding employee tenure can provide insights into company culture and retention. You can calculate the tenure of both past and current employees with DATEDIFF.

Calculating Tenure in Years

If you have an 'HR' table, you might want to know how many years an employee worked for the company. We need to handle both employees who have left and those who are still working. The TODAY() function is perfect for this, as it always returns the current date.

Create a new column in your 'HR' table called "Tenure in Years."

Tenure in Years = 
VAR EndDate = IF(ISBLANK(HR[LeaveDate]), TODAY(), HR[LeaveDate])
RETURN DATEDIFF(HR[HireDate], EndDate, YEAR)

Here's what this DAX formula does:

• We declare a variable EndDate to make the formula cleaner.
• It checks if the LeaveDate is blank. If it is, that means the employee is still active, so we use TODAY() as the end date. If it’s not blank, we use their LeaveDate.
• Finally, DATEDIFF calculates the difference in years between a person's HireDate and their EndDate.

A More Detailed Tenure Calculation (Years and Months)

Sometimes just seeing '5 years' isn't specific enough. You want to see "5 years and 3 months." This requires a slightly more advanced trick but delivers a much more user-friendly result.

Let's create another new column, "Detailed Tenure."

Detailed Tenure = 
VAR TotalMonths = DATEDIFF(HR[HireDate], IF(ISBLANK(HR[LeaveDate]), TODAY(), HR[LeaveDate]), MONTH)
VAR Years = INT(TotalMonths / 12)
VAR RemainingMonths = MOD(TotalMonths, 12)
RETURN
Years & " years, " & RemainingMonths & " months"

In this advanced example:

• First, we calculate the TotalMonths of service using DATEDIFF.
• Next, we find the number of full years by dividing TotalMonths by 12 and taking the integer part with INT().
• Then, we use the MOD() function to get the remainder of months after accounting for the full years. The modulo function is super useful for this kind of "leftover" math.
• Finally, we combine everything into a clean text string.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 3: Calculating Project Duration or Ticket Resolution Time

For service desks or project management teams, the time it takes to resolve an issue or complete a task is a critical KPI. In these cases, you might want a more granular measure, like hours or even minutes.

Imagine a 'SupportTickets' table with OpenedDateTime and ClosedDateTime columns.

Resolution Time (Hours) = DATEDIFF('SupportTickets'[OpenedDateTime], 'SupportTickets'[ClosedDateTime], HOUR)

This gives you a powerful metric to track team performance and service level agreements (SLAs).

Common Gotchas and Best Practices with DATEDIFF

While DATEDIFF is powerful, there's one common point of confusion that trips up many beginners. Being aware of it can save you a lot of time debugging your formulas.

The "Boundary Crossing" Trap

DATEDIFF does not calculate the number of full, 24-hour periods or full months. Instead, it counts the number of interval boundaries crossed between the start and end dates.

Here’s a clear example of what that means:

DATEDIFF("2023-12-31", "2024-01-01", YEAR)

The result of this calculation is 1.

Even though only one day has passed, because the date crossed a year boundary (December 31st to January 1st), DATEDIFF with the YEAR interval counts it as one year.

This behavior is most noticeable with these intervals:

• YEAR: Crosses December 31st.
• QUARTER: Crosses the end of March, June, September, or December.
• MONTH: Crosses the end of any month.

If you need to calculate a precise difference, like a person's age, you're usually better off calculating the total number of days and dividing. For example: ([EndDate] - [StartDate]) / 365.25. For most business reporting, however, the boundary-crossing behavior is acceptable and what people expect, we don't say we've worked somewhere for "4.75 years," we say "4 years."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using DATEDIFF in Measures

So far, we have only used DATEDIFF in calculated columns. Calculated columns compute their value for each row during data refresh and are stored in your model. This is great for static values you'd want to slice by, like employee tenure.

However, you can also use DATEDIFF inside a measure. Measures are calculated on the fly and respond to the filter context of your report (like slicers, filters on visuals, etc.).

For example, to calculate the average ticket resolution time, you would create a measure:

Avg Resolution (Days) = 
AVERAGEX(
    'SupportTickets', 
    DATEDIFF('SupportTickets'[OpenedDateTime], 'SupportTickets'[ClosedDateTime], DAY)
)

This measure dynamically calculates the average duration for whatever tickets are currently visible in your report, whether filtered by team, priority, or time period.

Final Thoughts

DATEDIFF is an essential function in your Power BI toolkit, providing you a flexible way to handle virtually any time-based calculation. By mastering its syntax and understanding its unique quirks, like the boundary-crossing rule, you can create more meaningful reports and uncover valuable insights that depend on analyzing durations.

Manually writing these DAX expressions for every calculation can be repetitive and time-consuming. We built Graphed to remove this friction entirely. Instead of wrestling with syntax, our users simply connect their data sources and ask questions in plain English like, "What was our average shipping time by product category last quarter?" Graphed generates the live dashboard for you in seconds, saving you from having to look up formulas so you can focus on finding answers.