How to Use DATEDIFF() in Power BI DAX? [With Conditions]

Last week, while working on a Power BI dashboard, I needed to calculate the difference between two dates using DAX. I researched this and found that the Power BI DATEDIFF() function is the best option for this purpose.

In this Power BI article, I will explain the Power BI DATEDIFF() function and its syntax. We will also see how to use DATEDIFF() in Power BI DAX and how to filter DATEDIFF() with conditions in Power BI DAX, with examples.

DATEDIFF() in Power BI DAX

Power BI DATEDIFF() in DAX helps to calculate the difference between two dates and returns the amount of time between them in the specified Interval, such as days, months, weeks, quarters, seconds, minutes, hours, or years.

The only requirements are that you know the start and end points in date or time format and that you can specify the type of interval you want.

Power BI DATEDIFF() Syntax:

DATEDIFF(Date1, Date2, Interval)

Where:

• Date1: The starting date.
• Date2: The ending date.
• Interval: The Interval of time you want the difference in, such as DAY, SECOND, MINUTE, HOUR, WEEKS, MONTH, QUARTER, or YEAR.

For example, if you want to find the number of days between two dates:

DATEDIFF('Table'[StartDate], 'Table'[EndDate], DAY)

This will give you the number of days between the StartDate and EndDate.

How to Use DATEDIFF() in Power BI DAX

Here, we will see how to use the DATEDIFF() function in Power BI DAX with the help of an example where we need to calculate the time to ship day using the order date and ship date.

For this example, I create a data set in Power BI that contains OrderID, CustomerName, OrderDate, ShipDate, Product, Quantity, and Price.

Now follow the below steps:

If you follow the same example, you loaded the above data set into Power BI Desktop. Then, you can see the column in the data pane.

Now go to Table view and click New column under the Table tools.

Then, in the formula bar, put the below DAX expression.

TimeToShip = DATEDIFF('Orders Table'[OrderDate], 'Orders Table'[ShipDate], DAY)

Where:

• TimeToShip: This is the name of the calculated column.
• DATEDIFF: This function calculates the difference between two dates.
• ‘Orders Table'[OrderDate]: This is the starting date, the date when the order was placed.
• ‘Orders Table'[ShipDate]: This is the ending date, the date when the order was shipped.
• DAY: This specifies that the difference should be calculated in days.

The table view shows the TimeToShip created, which is the difference between the order date and the ship date.

DATEDIFF() With Condition in Power BI DAX

Last week, I was working on a Power BI report for a project management dashboard. I received a requirement to calculate the number of days between the project start date and the project end date, but only for projects marked as “Completed.” So, let’s see how we can do it.

According to this scenario, I have a SharePoint list named ProwerPlatformProject that contains the following columns with various data types:

Columns	Data Types
ProjectID	Number
ProjectName (Title)	Single line of text
StartDate	Date and time
EndDate	Data and time
Status	Choice

Now load the above SharePoint list into Power BI Desktop. The column will appear in the data pane.

Now go to Table view and click New column under the Table tools.

Then, in the formula bar, put the below DAX expression.

ProjectDuration = 
IF(
    PowerPlatformProjects[Status] = "Completed", 
    DATEDIFF(PowerPlatformProjects[StartDate], PowerPlatformProjects[EndDate], DAY), 
    BLANK()
)

Where:

• ProjectDuration: This is the name of the calculated column.
• IF: This function checks a condition and returns one value if the condition is true and another value if it is false.
• PowerPlatformProjects[Status] = “Completed”: This condition checks if the project status is “Completed.”
• DATEDIFF(PowerPlatformProjects[StartDate], PowerPlatformProjects[EndDate], DAY): If the project is completed, this calculates the number of days between the project’s start date and end date.
• BLANK(): If the project status is not “Completed,” this returns a blank value.

Then, in the table view, a new column (ProjectDuration) was added to the data set.

Now, you can use this ProjectDuration column in your Power BI visualizations to analyze the duration of completed projects. This way, you can use DATEDIFF() in Power BI using DAX.

I hope you follow all the steps and have an idea of how to use DATEDIFF() in Power BI.

Also, you may like the below Power BI Tutorials:

• Filter Current Year Data Using Power BI DAX
• Power BI DAX Date Filter Not Working
• How to Filter Date Using Power BI DAX
• How to Use Power BI Weekday Function?