Users have a number of ways to calculate the difference between dates in Power BI. You could create a DATEDIFF DAX Formula, create a calculation in Power Query, or use the Custom Column from Example tool to avoid writing formulas altogether.
While it’s not as simple as subtracting two columns of dates from each other like you would in Microsoft Excel, Power BI gives you a number of ways to calculate the number of days between two columns.
To calculate the difference between two dates in Power BI, you can create a New Column and use the DATEDIFF DAX formula, use the Duration.Days formula in the Power Query editor , or avoid custom formulas altogether by using the Power Query Column from Example feature.
Regardless of which method you decide to use to calculate the difference between two dates, there are a couple fo pre-requisites that are helpful to keep in mind.
Let’s take a look and then jump in!
Table of Contents
Pre-Requisites for Calculating the Difference Between Dates in Power-BI
To be able to perform date calculations in Power BI, you need to make sure of a several pre-requisites in your data to avoid potential miscalculations and errors.
- Columns Must be a Date or DateTime Data Type – Power BI will only be able to perform calculations on dates if the columns are defined as dates. You can update the data type of a column by launching the Power Query editor, Right Click a column and select the new data type.
- Date Differences Must be Calculated on a Row by Row basis – While many formulas in Power BI will require you to aggregate data with formulas like SUM, FIRST, or AVERAGE, dates are different. Since you cannot SUM dates, you need to calculate the number of days between dates on a row by row basis either in Power Query or by Creating a New Column.
As long as columns are defined as dates or date time, and your calculations are performed on a row by row basis the following methods will be able to help you with your calculations.
Calculate the Difference Between two Dates using the DATEDIFF DAX Formula
Another method to calculate the number of days between two dates is to create a New Column using the DATEDIFF DAX formula.
The syntax for it is as follows:
ColumnName = DATEDIFF([Start Date], [End Date], Day)
Start Date and End Date will consist of the actual table name and field name. Let Power BI autocomplete the table and field names to save time from typing it.
Note: You cannot use DATEDIFF to create a Measure. You Must Create a New Column.
Measures are aggregations and require an aggregation method like Sum or Average. The difference between dates has to be calculated on a row by row basis. This means that you have to create a New Column prior to being able to use a DATEDIFF formula.
The DATEDIFF function can also be used for different time period calculations in Power BI.
Change the interval parameter to one of the following.
- DAY: Difference in days
- MONTH: Difference in months
- QUARTER: Difference in quarters
- YEAR: Difference in years
- HOUR: Difference in hours
- MINUTE: Difference in minutes
- SECOND: Difference in seconds
Note: You will need to define date time data types prior to being able to calculate a difference in hours, minutes or seconds.
What is Power BI Power Query and How to Launch It.
Power Query has been around since 2010. Originally introduced in Microsoft Excel, it has expanded into Power BI, Microsoft Analysis Services, and also exists as part of Microsoft Fabric in Power BI Online.
The fastest way to launch Power Query in Power BI is to right click on a table in the Data Panel of Power BI and choose Edit Query
The next screen is the Power Query Editor within Power BI. It looks very similar to Excel Get and Transform and most of the functionality is the same.
Now that Power Query is open, you can calculate the difference between two dates.
Our sample data includes an Order Date and a Ship Date for a fictional ice cream manufacturing company. We want to answer the question of how many days does it take from placing an Order to Shipping product.
Power Query Formula to Calculate the Difference Between Two Dates
If you prefer to write your own Power Query formula to calculate the number of days between two dates, use the following.
Duration.Days([End Date] – [Start Date])
The formula will calculate the number of days between the end date column and the start date column for each row in the dataset.
A great part of using Power Query to calculate the difference between two dates is that it works the same for Power BI as it does in Excel.
Calculate the Difference Between two Dates with Power BI Column From Example
Creating Columns from Example is one of our favorite features of Power Query. It lets you quickly get the results you need without having to create a formula of any kind.
After launching the Power Query editor, you can follow these steps.
Step 1.) Select both columns that you want to calculate the number of days between. You can select multiple columns by holding down Shift and clicking on each one.
Step 2.) Click on Column From Examples and choose From Selection.
The next screen will show you a blank Column1 at the far right of the dataset.
Double Click Into the Empty Field
This brings up a contextual menu with example date calculations available.
You can either select the calculation from the dropdown menu, or type in an example of the output.
In our example, we typed the Number 4 in the column to the far right a couple of times and Power BI generates and suggests a Power Query formula to identify the number of days between two dates.
Press Enter
The rest of the column will auto-fill down. Press the green OK button to save the step and save the newly created column with the date difference in it.
This method will also show you the Power Query code that’s generated for the custom calculation from example.
If you want to take your Power BI skills even further, Patrick from Guy in a Cube explains some cool features in Power BI when working with Time that can compliment date calculations.
Conclusion
Power BI gives users a number of ways to calculate the number of days, weeks, months, or years between two dates. Because the calculation has to be done on a row by row basis, our recommended method is to use the Power Query edtior.
Power Query is typically faster than performing calculations with DAX. You also have to use the Power Query editor to adjust field data types and it puts all of the steps needed into one place.
Quick calculations can be performed using the DAX DATEDIFF function, as long as its being used to create a new column. Date differences cannot be calculated using a measure because there is nothing to aggregate.
For an even faster, zero code way to calculate the number of days between dates, including only counting weekdays, weekends, last day of the month etc. try the Power Query Custom Column from Example functionality to automatically create a Power Query M formula.