How to Subtract Dates and Calculate Time Periods in Power BI

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

4. Name the new column “LeaveDuration”.
5. Change the column type to Whole Number (could be decimal as well)
6. 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

2. For working days only (excluding weekends):

Working Days = 
NETWORKDAYS(
    EmployeeLeave[StartDate],
    EmployeeLeave[EndDate]
)

3. 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

2. 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]
)

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.