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

[jgarcin8]

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! 

[jgarcin8]

Hi

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

[sevenhills · Accepted solution]

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]

Hi

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

The same issue with both formulas 😞 

[sevenhills]

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]

Hi,

Yes, I tried as "New column".

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

But now appears this:

[sevenhills]

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]

Hi

I changed the Dashboard to "Import mode" and the formula works! 

 Thank you so much!

[sevenhills]

Glad it worked in the end!

... sorry somtimes if we dont have code and error reproduce, it is tough ...