Table of Contents
To calculate the number of days between two dates in Power BI, you can use the DATEDIFF function. This function calculates the difference in days between two dates and returns the result as an integer. It takes three arguments: the date part to calculate (e.g. days), the start date, and the end date. By subtracting the start date from the end date, the function calculates the number of days between the two dates. This can be useful for analyzing time-based data and creating visualizations in Power BI.
Power BI: Calculate Days Between Two Dates
You can use the following syntax in DAX to calculate the number of days between two dates in Power BI:
Days Between = 1 * my_data[End Date] - my_data[Start Date]
This particular example creates a new column named Days Between that contains the number of days between the date in the End Date column of a table and the date in the Start Date column.
The following example shows how to use this syntax in practice.
Example: How to Calculate Days Between Two Dates in Power BI
Suppose we have the following table in Power BI named my_data that contains information on the start date and end date for various tasks at some company:
Suppose that we would like to create a new column that contains the number of days between the dates in the Start Date and End Date columns.
To do so, click the Table tools tab and then click the New column icon:
Then type the following formula into the formula bar:
Days Between = 1 * my_data[End Date] - my_data[Start Date]
This will create a new column named Days Between that contains the number of days between the dates in the Start Date and End Date columns:
From the output we can see:
- There are 29 days between 1/1/2024 and 1/30/2024.
- There are 0 days between 1/5/2024 and 1/5/2024.
- There are 4 days between 1/15/2024 and 1/19/2024.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: