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.
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:
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.
I would like be guided on how to get a timer to display number of days , hours, minutes and seconds since 2014/ May / 24th
Hi, do you mean like ticking timer in Power Bi? Or just to display how many days/ hours/ minutes and seconds elapsed since 24 May 2014? About ticking I don’t know. But you can get this information by adding a new measure, where you use datediff together with format expressions:
https://docs.microsoft.com/en-us/dax/format-function-dax –> https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function
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]]))
I would say you should just re-write this expression into DAX.
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.
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.
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?
Be sure you have created the same entity that holds free days information as in my solution.
Yes i am sure, I have created the same measure as you did.
I don’t mean just the measures, but also the entity with a list of free/working days.
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.
I believe what may help you is the DateDiff function: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff. So that if difference between your two datetime values is over or below 4hrs, you can calculate properly the number of taken days.
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!
Nevermind, I just found this great solution and it just works! https://exceleratorbi.com.au/calculating-elapses-business-hours-using-dax-part-2/
Thanks for sharing 😊
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)
Nah, it is returning FALSE if this is a working day and TRUE if weekend 🙂
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?
Why is it a problem?
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.
In Out Working Hours Branch
January 2 January 3 24.00 1
Month Working Days
Branch Working Days
1 1 (How can I make this still 24 instead of 1)
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.
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
I am not an expert in Power Bi 🙂
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.
Maybe just extend the calculation, that if result is -1, it returns 0 in fact? Or whatever else that fits 🙂
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
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
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
Nagesh Prakash Kotawadekar
Hi want to date difference between (order date)start date to todays date excluding weekend in power bi .
in excel using Networking days function but i wanted in power bi
Have you tried the approach described in powerusers forum? Eg. https://community.powerbi.com/t5/Desktop/NETWORKDAYS-type-function-in-PowerBI/m-p/154902?
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
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?
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.
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
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)
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.
Nhat Duong Nguyen
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.
You’re welcome 🙂
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 ?
No, sorry I am not able to help in that.