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

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:

     

    • sevenhills's avatar
      sevenhills
      Super User
      4 years ago

      Can you share the .pbix file ... ? (remove the sensitive information)

       

      Note: You can create with mockup data and then share please, sorry tough to diagnose without pbix file

      • 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!