Top
Power BI

Power BI DATEDIFF only working days, hours and so on…


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:

Datatable “Weekends”

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.


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

12 Comments
  • Gloria Nsereko

    I would like be guided on how to get a timer to display number of days , hours, minutes and seconds since 2014/ May / 24th

    November 6, 2019 at 2:26 pm Reply
  • Tushar

    Hi Tomaz,

    I have a problem with calculating aging for Defects between two dates excluding weekend. The formula i have works fine in excel how do i convert this in Power BI :

    =IF(([@[Today]]-[@[Creation time]])>5, ([@[Date Fixed]]-[@[Creation time]])/1.4,
    else ([@[Date Fixed]]-[@[Creation time]]))

    April 1, 2020 at 3:43 pm Reply
    • Tomasz Poszytek

      I would say you should just re-write this expression into DAX.

      April 6, 2020 at 9:39 pm Reply
  • Sam

    Very interesting. I’m currently struggling with this problem but need to take it a step further. I wonder if you have any suggestions

    I would like to count the minutes between 2 DateTime columns excluding weekends but only during working hours (e.g.08:30:00 to 05:30:00).

    My approach has been similar except I have a separate date table and have replaced true and false with 1 and 0 and used filter and sum on that column to get the days.

    For the time part, I created and additional column to do a similar thing for time (created a time table with IsWorkTime for each minute). The plan was to add the 2 columns together to get my answer. However this does not work if the StartTime is after the EndTime. This happens quite a lot if open and close are on different days.

    Any ideas?

    April 27, 2020 at 4:34 pm Reply
    • Tomasz Poszytek

      Right. But how can StartTime be ater EndTime? That’s a wrong logic… Anyways – if you can make it work having StartTime before EndTime, then simply add IF to check if StartTime <= EndTime and if yes - use it, if not, replace the variables with their places in your calculation.

      April 29, 2020 at 10:30 am Reply
  • Ben

    Hi, i have used this calculations , however seems it doesn’t work, I expected in the calculations:

    05/03/2019 09:47:00 am – 13/03/2019 10:15:00 am = 6.1

    the result from the measure you have provided just gave me as a result = 1

    could you please help me out?

    Regards

    May 23, 2020 at 2:02 pm Reply
    • Tomasz Poszytek

      Be sure you have created the same entity that holds free days information as in my solution.

      May 25, 2020 at 2:33 pm Reply
      • Ben

        Yes i am sure, I have created the same measure as you did.

        May 28, 2020 at 1:49 pm Reply
        • Tomasz Poszytek

          I don’t mean just the measures, but also the entity with a list of free/working days.

          May 31, 2020 at 10:30 pm Reply
  • priyanka

    Hi..!!
    I have created an app in powerapps and i am able to count the no.of days excluding weekends.But I need to calculate the half day(0.5)based on start time and end time like if the hours <4 it should be update by giving 0.5 as a result.

    June 17, 2020 at 2:26 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.