Table of Contents
Power BI offers a variety of tools and functions to help users calculate the number of days between two dates. One of the most commonly used methods is by creating a new calculated column in your data set that subtracts the start date from the end date and then formats the result as a number. This will give you the number of days between the two dates. Additionally, Power BI also has a DATEDIFF function that allows for more precise calculations, such as excluding weekends or only counting weekdays. Overall, Power BI provides a user-friendly and efficient way to calculate the number of days between two dates, making it a valuable tool for data analysis and reporting.
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: