Table of Contents
Power BI can calculate the number of days between a specific date and today by using the DATEDIFF function. This function calculates the difference between two dates and returns the result in days. To calculate the number of days between a specific date and today, the DATEDIFF function would use the specific date as the start date and the TODAY function as the end date. This would provide the number of days between the specific date and the current date, allowing for easy analysis and visualization of time-based data in Power BI.
You can use the following syntax in DAX to calculate the number of days between a date and today in Power BI:
Difference = DATEDIFF('my_data'[Date], TODAY(), DAY)
This particular example creates a new column named Difference that contains the number of days between the date in the Date column of a table named my_data and today’s date.
The following example shows how to use this syntax in practice.
Example: How to Calculate Days Between Date and Today in Power BI
Suppose we have the following table in Power BI named my_data that contains information about the total sales made on various dates at some company:
This article is being written on 1/8/2024.
Suppose that we would like to create a new column that contains the number of days between the dates in the Date column and today’s date.
To do so, click the Table tools tab and then click the New column icon:
Then type the following formula into the formula bar:
Difference = DATEDIFF('my_data'[Date], TODAY(), DAY)
This will create a new column named Difference that contains the number of days between the dates in the Date column and today’s date of 1/8/2024:
From the output we can see:
- There are 737 days between 1/1/2022 and today’s date of 1/8/2024.
- There are 604 days between 5/14/2022 and today’s date of 1/8/2024.
- There are 568 days between 6/19/2022 and today’s date of 1/8/2024.
Note: You can find the complete documentation for the DATEDIFF function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: