How to Subtract Dates and Calculate Time Periods in Power BI

by

Question from Reader: “I have employee leave records with start and end dates. How can I calculate the number of days between these dates in Power BI? I need to analyze leave duration patterns, but I’m struggling to handle the date calculations correctly. Sometimes I get negative values or incorrect results.”

Whether you’re tracking employee leave, project durations, or aging metrics, understanding how to handle date math in Power BI is essential. Let’s explore multiple approaches using both Power Query and Power BI DAX.

For this tutorial, we’ll work with a table named “EmployeeLeave” containing columns: EmployeeID (text), LeaveName (text), StartDate (date), EndDate (date), LeaveType (text), Department (text).

Calculate Date Differences Using Power Query

  1. In Power BI – hit Transform Data to launch the Power Query Editor.
  2. Select your date columns.
  3. Add a new Custom Column with this formula:
= Duration.Days([EndDate] - [StartDate]) + 1
  1. Name the new column “LeaveDuration”.
  2. Change the column type to Whole Number (could be decimal as well)
  3. You have now calculated the time duration since start date.

Create Date Calculations Using DAX

To calculate date duration between two BI columns, proceed as following:

  1. Create a basic measure for leave duration:
Leave Days = 
DATEDIFF(
    EmployeeLeave[StartDate],
    EmployeeLeave[EndDate],
    DAY
) + 1
  1. For working days only (excluding weekends):
Working Days = 
NETWORKDAYS(
    EmployeeLeave[StartDate],
    EmployeeLeave[EndDate]
)
  1. Calculate days from today to leave start:
Days Until Leave = 
DATEDIFF(
    TODAY(),
    EmployeeLeave[StartDate],
    DAY
)

Creating Visual Analytics

  1. Create a matrix visual showing:
  • Employee names in rows
  • Leave duration in values
  • LeaveType as columns
  1. Add conditional formatting to highlight:
  • Leaves longer than 5 days in red
  • Leaves between 3-5 days in yellow
  • Leaves under 3 days in green

Advanced Date Pattern Analysis

  1. Create a measure for average leave duration by department:
Avg Department Leave = 
AVERAGEX(
    SUMMARIZE(
        EmployeeLeave,
        EmployeeLeave[Department]
    ),
    [Leave Days]
)

Frequently Asked Questions

Q: Why do I get different results when calculating the same date range?
A: This usually occurs due to inclusive vs exclusive date counting. Adding +1 to your DATEDIFF calculation makes it inclusive of both start and end dates, which is typically desired for leave calculations.

Q: How do I handle overnight shifts that cross midnight?
A: For shifts spanning multiple days, consider using DATEDIFF with HOUR or MINUTE units, then convert back to days. This approach better handles scenarios where EndDate might be the next day but represents the same work period.

Q: Can I exclude specific holidays from my calculations?
A: Yes, create a separate Holidays table and use NETWORKDAYS.INTL or build custom logic that subtracts holiday counts from your date difference calculations.

Advanced Date Scenarios and Alternative Approaches

Handling Multiple Leave Types with Different Calculation Rules

Different leave types often require specific calculation methods. For example, you might need to exclude weekends for vacation leave but include them for medical leave:

daxDynamic Leave Days = 
SWITCH(
    EmployeeLeave[LeaveType],
    "Vacation", NETWORKDAYS(EmployeeLeave[StartDate], EmployeeLeave[EndDate]),
    "Medical", DATEDIFF(EmployeeLeave[StartDate], EmployeeLeave[EndDate], DAY) + 1,
    "Personal", NETWORKDAYS(EmployeeLeave[StartDate], EmployeeLeave[EndDate]),
    DATEDIFF(EmployeeLeave[StartDate], EmployeeLeave[EndDate], DAY) + 1
)

Excel vs Power BI Date Calculations

If you’re migrating from Excel, note these key differences:

  • Excel’s NETWORKDAYS function automatically excludes weekends by default
  • Power BI’s NETWORKDAYS requires explicit weekend parameter specification
  • Excel handles null dates differently, often returning 0 instead of errors
  • Time zones can affect TODAY() calculations between Excel and Power BI Service

Calculating Partial Days and Time Components

For more granular analysis, you might need to account for time portions within dates:

daxLeave Hours = 
DATEDIFF(
    EmployeeLeave[StartDate],
    EmployeeLeave[EndDate],
    HOUR
) + 24  // Add 24 to include the final day

Troubleshooting Common Issues

  • Negative durations: Ensure EndDate is always later than StartDate using Power Query filter
  • Incorrect weekend calculations: Verify your NETWORKDAYS calendar settings
  • #Error in calculations: Check for null dates using ISBLANK() function
  • Inconsistent results: Validate date formats are consistent across your dataset

For more complex date operations and pattern analysis, you might want to create a proper date dimension table. This enables more sophisticated time intelligence calculations.