Calculate number of days between two days in Power BI
To find the number of days in between two dates in Power BI use the DAX DATEDIFF function:
- Open your Power BI Report.
- Go to the Data View and highlight your table.
- Use the following DAX statement (modify the table and date column names according to your needs):
Days_between (DATEDIFF('Dates'[Date_1],'Dates''[Date_2],DAY)
- Check your table results and save your model.
Find date differences in PowerBI – A Practical Example
- In Power BI desktop We will first create a very simple table called Dates_Turotial with two columns: Course Start Date and Course End Date.
- Here is a screenshot of our data
- We will now go ahead and calculate the raw difference between the course Satart and Finish:
Duration (days) = DATEDIFF('Dates_Tutorial'[Course Start Date], 'Dates_Tutorial'[Course Finish Date],DAY)
- Here’s a screenshot of our Data View:
- An alternative way to accomplish the same, is to use a simple Power BI column subtraction:
Duration (days) = 'Dates_Tutorial'[Course Finish Date]- 'Dates_Tutorial'[Course Start Date]
- Note: If you are using this method – note that you will need to convert the Duration column to whole or decimal numbers.
Calculate time differences excluding Weekends
To calculate the number of days between two data times, but exclude weekends and calendar holidays, use the NETWORKDAYS DAX function as shown in the example below:
Duration (workdays) = NETWORKDAYS ('Dates_Tutorial'[Course Start Date], 'Dates_Tutorial'[Course Finish Date].[Date])
As you can see when excluding non working days, the gap between the course start and end date is smaller:
Get time Differences in Month units
In the same fashion, we can extract time gaps in months:
Duration (months) = DATEDIFF ('Dates_Tutorial' [Course Start Date], 'Dates_Tutorial'[Course Finish Date], MONTH)