Forum Discussion

jgarcin8's avatar
jgarcin8
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's avatar
    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's avatar
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

  • jgarcin8's avatar
    jgarcin8
    Frequent Visitor
    4 years ago

    Hi

    Thank you for the quick reply. Im having the next issue:

     

    The same issue with both formulas 😞 

    • sevenhills's avatar
      sevenhills
      Super User
      4 years ago

      a) Did you try to do as New Column? 

       

      b) 'Query2'[IsWeekDay] = "False" ... change this.

               it is saying the data types are wrong

       

      If this does not work, share the data types

      • jgarcin8's avatar
        jgarcin8
        Frequent Visitor
        4 years ago

        Hi,

         

        Yes, I tried as "New column".

         

        I changed to  'Query2'[IsWeekDay] = "False" ... and seems for that sentence works.

         

        But now appears this: