How to find the number of days between two dates in Power BI?

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:
Duration (days) = 'Dates_Tutorial'[Course Finish Date]- 'Dates_Tutorial'[Course Start Date]

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)