Forum Discussion
Time difference between two date/times excluding weekends and holidays
Hello Power BI experts!
I am looking for help to add a column that calculates the # of hours or days (not whole) between two date/times. I have included below a sample of the date/times I have and also have work days marked with a 1 and weekends and holidays with a 0 in my calendar. Either hours or days would work as I can calcualte hours back to days but whole days will not work.
I'm sorry if this has been answered before as I did search and tried several ways but have nothing seemed to work or fit for me and I am getting myself confused :( . Any help on how to do this would be greatly appreciated.
Thank you!
Samples of my dates:
| Date/Time Opened | Date/Time First Resolved |
| 9/26/2018 11:44 | 9/26/2018 14:47 |
| 9/9/2018 14:37 | 9/18/2018 8:55 |
| 8/8/2018 9:06 | 9/7/2018 10:24 |
| 9/10/2018 14:41 | 9/10/2018 15:11 |
| 9/9/2018 14:37 | 9/18/2018 8:55 |
| 9/26/2018 8:28 | 9/26/2018 9:22 |
Anonymous,
You can create a calculated column using DAX below.Total Working Days Column = SUMX ( FILTER ( 'Calendar', 'Calendar'[Date] >= Table[Date/Time Opened] && 'Calendar'[Date] <= Table[Date/Time First Resolved] ), 'Calendar'[isWorkDay] )
However, if you need to calculate work hours between two date time valyues, please check the Power Query function in this similar thread: https://social.technet.microsoft.com/Forums/en-US/f3fc729e-a2ec-409a-b20e-b5cf8ff5dc2a/calculate-work-hours-between-two-timedate-stamps?forum=powerquery.
Regards,
Lydia
5 Replies
Anonymous,
You can create a calculated column using DAX below.Total Working Days Column = SUMX ( FILTER ( 'Calendar', 'Calendar'[Date] >= Table[Date/Time Opened] && 'Calendar'[Date] <= Table[Date/Time First Resolved] ), 'Calendar'[isWorkDay] )
However, if you need to calculate work hours between two date time valyues, please check the Power Query function in this similar thread: https://social.technet.microsoft.com/Forums/en-US/f3fc729e-a2ec-409a-b20e-b5cf8ff5dc2a/calculate-work-hours-between-two-timedate-stamps?forum=powerquery.
Regards,
Lydiahi,
I am working on the same thing. I have taken workdays as 1 and weekend and Holidays as 0, used mentioned DAX, but I am getting right for weekends and holidays as right value, but for weekdays +1 is added to the value which is not accpetable.
Ex. Weekday dates
Start date 14-11-2019
End date 16-11-2019
Difference should be 2 days but it is taking as 3 days, because it is summing from workday which is marked as 1 for weekdays. I am not sure where I am making an error.
Please let me know how to overcome this.
Plz share DAX code I am also facing same issue . please help
Anonymous did you created any calendar table,if yes - can you please share the DAX
Yes, you need to create a calendar table:
Calendar = ADDCOLUMNS( CALENDAR(DATE(2018, 1, 1), DATE(2018, 12, 31)), "IsWorkDay", IF(WEEKDAY([Date], 2) < 6, 1, 0) )
