Forum Discussion

andyL's avatar
andyL
New Member
3 years ago
Solved

Calculating the time between two dates in days excluding weekends and back holidays

I have a set of data that contains the date/time when the issue was logged amongst a number of other fields.

 

I want to be able to work out the length of time the issue has been outstanding excluding weekends and bank holidays. I have read some of the posts around this but struggling to get it to work with english date formats. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi andyL ,

     

    I think you just need to create a calendar table as below.

    You may referv to this blog to learn more details about working day calendar.

    Then create a measure as below.

    Measure = 
VAR _DATELIST = CALCULATETABLE(VALUES('Calendar'[Date]),FILTER('Calendar','Calendar'[Date] >= MAX('Table'[Start Date]) && 'Calendar'[Date]<=MAX('Table'[End Date])&&'Calendar'[working day] = "working day"))
RETURN
COUNTAX(_DATELIST,[Date])

    Result:

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi andyL ,

     

    I think you just need to create a calendar table as below.

    You may referv to this blog to learn more details about working day calendar.

    Then create a measure as below.

    Measure = 
VAR _DATELIST = CALCULATETABLE(VALUES('Calendar'[Date]),FILTER('Calendar','Calendar'[Date] >= MAX('Table'[Start Date]) && 'Calendar'[Date]<=MAX('Table'[End Date])&&'Calendar'[working day] = "working day"))
RETURN
COUNTAX(_DATELIST,[Date])

    Result:

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.