I recently found myself in a situation, when I needed to calculate a date difference between two datetime values in Power BI excluding weekends. The request seemed first as an easy thing, until I found out, that Power BI doesn’t have any function allowing to achieve it easily.

I found many solution on the community forum, eg. https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662 or https://community.powerbi.com/t5/Desktop/calculate-number-of-working-days-between-2-dates/td-p/55456 and even more when googling, but all of them were simply returning a number of working days between two dates.

Because I needed a very precise information, like if the beginning date was 2018-10-25 14:00 and ending was 2018-10-25 15:00 then I wasn’t expecting to receive “1 day” in return but “0”, the same if the beginning date was 2018-10-25 14:00 and ending was 2018-10-26 13:59 – that still should be “0”. And that is what “DATEDIFF(Start, End, DAY)” function in Power BI returns. But DATEDIFF also counts in weekends, so in case the beginning date was 2018-10-26 14:00 and ending was 2018-10-29 15:00 it would return 3, when I expected 1.

During my research I also found this smart post: https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-powerapps/ that I was able to use to count number of days, but still I wasn’t able to count number of hours or minutes.

The solution

All approaches I found were focused on counting number of working days. I realized, that what I need for my scenario is the number of weekends, that are between my starting and ending dates.

Therefore I also created a datatable containing dates from 1st January 2017 to 30th December 2027, with all weekends selected:

I have created the table using Excel file as a datasource. In the file column showing whether the corresponding date is a weekend, or not, contains the below expression:

=IF(WEEKDAY(A2,2)>5,FALSE,TRUE)

Then I simply used the “DATEDIFF” function and after subtracted number of days/ hours/ minutes according to number of weekends that had occurred between the dates.

The formula I am using looks like below. For days:

DATEDIFF([StartDateTime],[EndDateTime], DAY) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE(),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )
)

Then for hours:

DATEDIFF([StartDateTime],[EndDateTime], HOUR) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE(),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )*24  --24 hours per day
)

And for minutes:

DATEDIFF([StartDateTime],[EndDateTime], MINUTE) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE(),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )*1440  --24 hours per day * 60 minutes per hour
)

This approach works for my scenario like a charm. Now I have the precision with which “DATEDIFF” function calculates difference between two dates and the result is not affected by the weekends.