Forum Discussion

Frequent Visitor

4 years ago

Solved

Get the difference between 2 dates excluding weekends. Storage Mode: Direct Query

Hello, Im trying to calculate the difference in days between 2 dates: 'SubmittedDate' and 'LastModifiedDate' excluding weekends. I have 2 sql tables here: *Table 'Query1' in which I ha...

Need Help

sevenhills

4 years ago

Try this and see if it works (as column)

Working Days Calc = 
DateDiff ('Query1'[SubmittedDate], 'Query1'[LastModifiedDate], Day) -
(
   CALCULATE (
          countrows('Query2'),
          'Query2'[IsWeekDay] = FALSE(),
          DatesBetween('Query2'[Date], 'Query1'[SubmittedDate], 'Query1'[LastModifiedDate] - 1)
   )
)

Other way (simplified)

Working Days Calc = 
   CALCULATE (
          COUNTROWS('Query2'),
          DatesBetween('Query2'[Date], 'Query1'[SubmittedDate], 'Query1'[LastModifiedDate] - 1), 
          'Query2'[IsWeekDay] = FALSE(),
          All('Query2')
  )

The only thing you may need to consider based on your requirement is to do "-1", to include the end day or not, depends on your requirements

sevenhills

Super User

4 years ago

Try this and see if it works (as column)

Working Days Calc = 
DateDiff ('Query1'[SubmittedDate], 'Query1'[LastModifiedDate], Day) -
(
   CALCULATE (
          countrows('Query2'),
          'Query2'[IsWeekDay] = FALSE(),
          DatesBetween('Query2'[Date], 'Query1'[SubmittedDate], 'Query1'[LastModifiedDate] - 1)
   )
)

Other way (simplified)

Working Days Calc = 
   CALCULATE (
          COUNTROWS('Query2'),
          DatesBetween('Query2'[Date], 'Query1'[SubmittedDate], 'Query1'[LastModifiedDate] - 1), 
          'Query2'[IsWeekDay] = FALSE(),
          All('Query2')
  )

The only thing you may need to consider based on your requirement is to do "-1", to include the end day or not, depends on your requirements