Forum Discussion
Time difference between two date/times excluding weekends and holidays
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
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
hi,
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) )
