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 have the fields "'SubmittedDate' and 'LastModifiedDate'

*Table 'Query2'  which is a Calendar table and I have a column ('IsWeekday') to identify Saturdays and Sundays.

Saturdays and Sundays ='False'

Monday to Friday='True'

 

How Can I combine these 2 tables to get the diff in days?  My tables are in storage mode: DirectQuery so Im having problems with "calculate" or "count" functions.

 

Really appreciate you help amitchandak 

Thank you so much! 

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

8 Replies

  • 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

    I changed the Dashboard to "Import mode" and the formula works! 😁 Thank you so much!