Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hi
I changed the Dashboard to "Import mode" and the formula works! 😁 Thank you so much!
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
Hi
Thank you for the quick reply. Im having the next issue:
The same issue with both formulas 😞
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
Hi,
Yes, I tried as "New column".
I changed to 'Query2'[IsWeekDay] = "False" ... and seems for that sentence works.
But now appears this:
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
Hi
I changed the Dashboard to "Import mode" and the formula works!
Thank you so much!
Glad it worked in the end!
... sorry somtimes if we dont have code and error reproduce, it is tough ...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |