How to Calculate Date Difference in Power BI?

Do you know how to calculate date difference in Power BI? We can use the DATEDIFF() function to calculate the date difference in Power BI.

I was recently working on a Power BI logistic report. With the help of the DATEDIFF function, I can easily calculate the difference between two dates, enabling easy analysis of time intervals such as days, months, or years.

This Power BI tutorial teaches how to calculate the date difference between two columns, compute the date difference using Power BI measures, and find the date difference from today using Power BI.

Additionally, we learn how to calculate the date difference in Power Query Editor within Power BI.

How to Calculate Date Difference in Power BI

Now we see how to find the date difference between two columns in Power BI.

Example

You work for a human resources department in a company that manages employee performance reviews. Your company wants to analyze the time gap between each employee’s scheduled review date and completion date.

We require two dates to find the review completion delay: Scheduled Review Date and Actual Completion Date.

Here, we have a SharePoint list (Employee Performance Review Data) that contains below columns with various data types:

ColumnsData Types
Employee IDNumber
Employee NameSingle line of text
Scheduled Review DateDate and time
Actual Completion DateDate and time
Power bi date difference between two columns

In Power BI, we can use the DATEDIFF function to calculate the difference in days between two date columns. To do this, follow the below steps:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

Power bi date difference

2. Go to the Table view. Then, under the Home tab, click New column.

date difference between two columns in Power bi

3. In the formula bar, click the below expression. Then click Commit.

Review Completion Delay (Days) = DATEDIFF('Employee Performance Review Data'[Scheduled Review Date], 'Employee Performance Review Data'[Actual Completion Date], DAY)

Where:

  • Review Completion Delay (Days) = This is the name given to the new calculated column we created in our dataset.
  • DATEDIFF = It’s a function that calculates the difference between two dates.
  • ‘Employee Performance Review Data'[Scheduled Review Date] = This specifies the column containing the scheduled review dates in our dataset.
  • ‘Employee Performance Review Data'[Actual Completion Date] = This specifies the column containing the actual completion dates in our dataset.
  • DAY = It specifies that we want the difference in days.
power bi calculate date difference in a measure

4. Next, one Review Completion Delay (Days) column is added to the data set.

Date Difference Between Two Columns calculation in Power BI

This way, you can find the date difference between the two columns in Power BI.

Calculate the Date Difference in the Power BI Measure

I will tell you how to calculate date differences in the Power BI measure.

Example

You work for a logistics company that delivers products to customers. Your company wants to track the average delivery time to deliver products to customers after they place an order. This information will help identify any delays in the delivery process and ensure timely deliveries to customers.

To perform this calculation, you require two dates: Order Date and Delivery Date.

How to calculate date difference in a measure in power bi

1. Open Power BI Desktop and load the data. Then navigate to the Home tab and click ‘New measure.’

Power BI Date Difference Average

2. In the formula bar, click the below expression. Then click Commit.

Average Delivery Day =
AVERAGEX(
		'Walmart Data',
		DATEDIFF(
			'Walmart Data'[Order Date],
			'Walmart Data'[Delivery Date],
			DAY
		)
	)

Where:

  • Average Delivery Day = This is the name of the calculated measure being defined, representing the average number of days it takes for orders to be delivered.
  • AVERAGEX = calculates the average of an expression for each row in a table.
  • Walmart Data = Refers to the table name from which the data is retrieved.
  • DATEDIFF = It is used to calculate the difference between two dates.
  • ‘Walmart Data'[Order Date] = Refers to the column in the table containing the order dates.
  • ‘Walmart Data'[Delivery Date] = Refers to the column in the table containing the delivery dates.
  • DAY = Specifies the unit of time (in this case, days) for which the difference between dates is calculated.
calculate date difference in a measure in power bi

3. Under the Home tab, expand Visual gallery (black box) -> Click the Table visual.

Calculate difference between two datetime values in Power BI

4. Then, use the +Add data option to add the Order Date, Delivery Date, and Average Delivery Day into the Columns field.

How To Use measure DATEDIFF In Power BI

5. Then, see the average delivery day in the Power BI table visual.

Calculate the Date Difference in Power BI

This way, you can calculate date differences in the Power BI measure.

Power BI Date Difference from Today

Now we see how to calculate the date difference from today in Power BI.

Example

You manage a project management system for a construction company. Your company is responsible for completing various construction projects, and you need to track the progress of each project, including the number of days remaining until the deadline.

We require one date column to achieve this calculation: Project Deadline Date.

Here, we have a SharePoint list (Construction Project) that contains below columns with various data types:

ColumnsData Types
Project IDNumber
Project NameSingle line of text
Project Start DateDate and time
Project DeadlineDate and time
Calculating Days Between a Date and Today in Power Bi

Follow the below steps to calculate the date difference from today in Power BI:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

Datediff between latest date and todays date in Power BI

2. Go to the Table view. Then, click the New column under the ‘Table tools’ tab.

Calculate date difference vs today date in Power Bi

3. In the formula bar, click the below expression. Then click Commit.

Days Remaining = DATEDIFF( TODAY(),'Construction Project'[Project Deadline], DAY)

Where:

  • Days Remaining = This is the name given to the calculated column we created in our dataset.
  • DATEDIFF() = This function calculates the difference between two dates.
  • TODAY() = This function returns today’s date.
  • ‘Construction Project'[Project Deadline] = This specifies the column containing the project deadlines in our dataset.
  • DAY = It specifies that we want the difference in days.
How to calculate the difference between date and today

4. Next, the Days Remaining columns are added to the data set. With the help of this column, your company stays on track with project timelines.

Power BI Date Difference from Today

You can easily calculate the date difference from today in Power BI using the above steps.

Power BI Date Difference in Query Editor

This example shows how to use Power BI Power Query Editor to calculate the date difference between Project Start Dates and Project Deadlines.

I hope you load data to the Power BI desktop and your data set has these columns: Project Start Dates and Project Deadline.

1. In the Power BI desktop, Under the Home tab, click on the transform data to open the Power query editor.

Power Query Date Difference in Power Bi

2. Under the Add Column tab, click on the Custom Column in the power query editor.

Calculate difference between two dates power query in Power BI

3. After that, the Custom Column dialog box will appear. Give a name to the column, such as “Total Days.” Then, in the Custom column formula bar, input the following expression. Then click OK.

= Duration.Days([Project Deadline]-[Project Start Date])

Where:

  • Duration.Days = Power BI M query function
  • Project Deadline = Name of the Column
  • Project Start Date = Name of the Column
How to use date diff in Power BI Power Query (M query)

4. Once you click OK, you can see the custom column(Total Days) added in the power query.

Power BI Date Difference in Query Editor

This way, you can use Power BI date difference in Power Query Editor.

Conclusion

This Power BI tutorial covered various methods for calculating date differences.

In this article, we learned how to calculate the date difference between two columns, compute the date difference using Power BI measures, and find the date difference from today using Power BI.

Additionally, we learned how to calculate the date difference in Power Query Editor within Power BI.

Moreover, you may like some more Power BI tutorials: