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.

39 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
  • Giovany

    I was able to get the exactly same dax formula. But I was wonder, what happen if the start date is in a weekend already? The calculate will take the complete weekend, which would be wrong.

    The scenario would be, start date is saturday at 2 pm and the end date is monday at 9 am. If I use the hours I would have -39 hours! The result should be 9 hours.

    Do you have any ideas?

    Thanks for sharing!

    October 7, 2020 at 10:19 am Reply
  • Wisam

    Thanks for the article, Tomasz! Very helpful.

    Shouldn’t the Excel equation for IsWeekend be the other way around? Meaning =IF(WEEKDAY(A2, 2) > 5, TRUE, FALSE) instead of =IF(WEEKDAY(A2, 2) > 5, FALSE, TRUE)

    October 13, 2020 at 6:56 pm Reply
    • Tomasz Poszytek

      Nah, it is returning FALSE if this is a working day and TRUE if weekend 🙂

      October 19, 2020 at 11:30 am Reply
  • Miko Manalo

    I used the above and working but the problem is hours converted into whole number even I set the format to decimal. Can you help me with this?

    November 5, 2020 at 1:38 pm Reply
    • Tomasz Poszytek

      Why is it a problem?

      November 9, 2020 at 11:51 am Reply
      • Miko

        got the value I needed to be reflected which are not rounded off.

        By the way, I have another problem: On the table, if I use the month column it is showing the total number of workdays as expected and calculations are okay. But I when I used another category in the column, it will show the count of occurence falls on that particular date. How can I have the total working days regardless of the column category.

        Example:

        In Out Working Hours Branch
        January 2 January 3 24.00 1

        Month Working Days
        January 25

        Branch Working Days
        1 1 (How can I make this still 24 instead of 1)

        November 12, 2020 at 7:31 am Reply
  • Nadir Shakeel

    So i have a similar problem – I need to reverse engineer this and see the date a task should be completed. I can calculate the number of work days by using a date table and adding the number of work days by using the following TotalWorkDays = CALCULATE(SUM(‘Date'[Holidays]), DATESBETWEEN(‘Date'[Date], Combined[SLAStartwithHolidays], Today())). In the date table each workday is represented with a 1 and a weekend with a 0.
    My problem is that i have a start date, i need to calculate the ‘proposed completion date’ by adding in 5 working days to the start date and getting the corresponding date from the date table.

    November 11, 2020 at 9:40 pm Reply
  • Iris

    Crafty Solution but it doesn’t work for processes which happen outside working hours. Like for instance if you receive a request outside working ours and an SLA only applies to working hours

    April 6, 2021 at 12:33 pm Reply
  • Glen Vickers

    Thank you for this. I’m stuck on something though. I’ve got your math up in there and it works like a charm. Except when the date up to the hour matches. In those cases I get a result of -1 without the *24 for the hour datediff. If I put that in well -1*24 is …

    What I noticed in the data is the date is the same when this happens. however in some cases the minute is different and sometimes the second. But it always happens when the date and time are the same up to the hour.

    Suggestions? I need it at the hour level to measure MTTR with some hour targets.

    June 29, 2021 at 9:24 pm Reply
    • Tomasz Poszytek

      Maybe just extend the calculation, that if result is -1, it returns 0 in fact? Or whatever else that fits 🙂

      June 30, 2021 at 3:19 pm Reply
  • agus

    hello my english is bad but i am going to try explain this issue that i hope you can help me please

    i have a table with the next structure from 1 month of data

    hostname date status
    ——————————–
    machine 1 3/4/2021 10:00pm down
    machine 1 3/4/2021 11:00pm up
    machine 1 3/4/2021 12:00am up
    machine 1 3/5/2021 01:00am up
    .
    .
    .
    machine 2 3/4/2021 10:00pm up
    machine 2 3/4/2021 11:00pm up
    machine 2 3/4/2021 12:00am up
    machine 2 3/5/2021 01:00am up
    .
    .
    machine 3 3/4/2021 10:00pm down
    machine 3 3/4/2021 11:00pm down
    machine 3 3/4/2021 12:00am down
    machine 3 3/5/2021 01:00am down
    .
    .
    machine n 3/4/2021 10:00pm n
    machine n 3/4/2021 11:00pm n
    machine n 3/4/2021 12:00am n
    machine n 3/5/2021 01:00am n

    could you guide me how I can get the the machines that have been during LAST 5 days complete ( check that every day have some rows with diferents status) with status down
    Thanks in advance

    July 6, 2021 at 8:38 pm Reply
    • Tomasz Poszytek

      Hi, I would advise you do a visual and then add a date slicer, so that user will be able to define for which dates/ period they would like to see a list of completed machines: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

      July 15, 2021 at 9:57 am Reply
      • agus

        hi
        thanks for answer
        sorry i think that i did not explain correctly . my expected results is get machines that have been with status down the last 5 days

        hostname
        machine 1
        machine3
        machine x
        machine x
        machine x

        July 15, 2021 at 1:39 pm Reply
  • Nagesh Prakash Kotawadekar

    Hi want to date difference between (order date)start date to todays date excluding weekend in power bi .

    Please assist

    in excel using Networking days function but i wanted in power bi

    July 29, 2021 at 8:42 am Reply
  • Tapendar

    Hi Tomasz,

    im having State begin and State end columns are their.Those column representation is down time of a machine.
    how can i calculate runtime .can you help me to resolve the issue.

    Thanks in Advance

    August 27, 2021 at 12:47 pm Reply
  • Anna

    I’m calculating the minutes it takes to close support tickets while excluding weekends and holidays; my goal is to create KPIs to monitor associate performance. Your DAX works perfectly except when the ticket is opened or closed on a weekend or holiday, when this occurs my calculation if off and often results in a negative value.

    I’ve been trying to figure out the best way to accommodate for this but so far it looks like I may just need to exclude these from my data sets. Do you have any thoughts on how to account for these outliers?

    January 25, 2022 at 7:19 pm Reply
    • Tomasz Poszytek

      Hi, sorry I am not working with Power Bi on a daily basis, so that’s nothing I can help you with ad-hoc and unfortunately I have no prepared solution.

      February 7, 2022 at 3:11 pm Reply
  • jg

    Hi! I have to estimate how many tickets have been open for more than 90 working days. This code works, but I dont know how to consider only working days

    FUNCTION=

    var currentdate = max(‘Calendar'[Date])

    var activetickets>90 = calculate(countrows(tickets),all(‘Calendar’),tickets[Issue Date]curentdate ||ISBLANK(tickets[ApprovalDate]),datediff(tickets[Issue Date],curentdate,day)>90)

    RETURN activetickets>90

    February 16, 2022 at 10:28 am Reply
    • Tomasz Poszytek

      Following my approach you should have the table with only working days. And then to use it to find tickets opened for more than 90 days.

      February 18, 2022 at 2:33 pm Reply
  • Nhat Duong Nguyen

    Hi Tomasz,
    Life Saver, I have spent the whole day to go through all the solution, but yours works best in my case.
    Thank you so much.
    Thanks,
    Dzu

    May 14, 2022 at 12:49 am Reply
  • Fred

    I have a measure that calculates in seconds the difference between two dates and I would like to exclude vacations and public holidays by relying on an exchange calendar stored in another table. could you help me ?

    February 22, 2023 at 1:41 pm Reply

Post a Comment

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