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:

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.

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

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.

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

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.

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

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.

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

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

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

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:

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.

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

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.

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.

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.

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

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

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

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:

• Power BI SUM Measure
• Power BI Measure IF Blank Then 0
• Power BI Conditional Formatting based on Measure

Preeti Sahu

Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.