Forum Discussion
Helper ITrying to find a way to exclude weekends using DateDiff with dates from two tables
try 🙂 but better to try to filter out values from shipmentlinks table that hav no relations
var _deliveredDate = RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered]) var _clearedDate = RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared]) RETURN IF(ISBLANK(_deliveredDate) || ISBLANK(_clearedDate), 0, datediff(RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered].[Date]), RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared].[Date]),DAY) - IF( _deliveredDate < _clearedDate, countrows(FILTER(calendar(_deliveredDate , _clearedDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")), countrows(FILTER(calendar(_clearedDate, _deliveredDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")) ) +1 )do not hesitate to give a kudo to useful posts and mark solutions as solution
Community Championhoe many days it should be between 10/25 and 10/30?
25oct - Fri
28oct- Mon
29 - Tue
30-Wed
totally- 4.
11/16 - 11/16 - it is the one day.
if you need 3 days n the first case and 0days in the second- just remove "+1"from formula
var _deliveredDate = RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered])
var _clearedDate = RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])
RETURN
IF(ISBLANK(_deliveredDate) || ISBLANK(_clearedDate), 0,
ABS(datediff(_deliveredDate, _clearedDate, DAY))
-
IF(
_deliveredDate < _clearedDate,
countrows(FILTER(calendar(_deliveredDate , _clearedDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")),
countrows(FILTER(calendar(_clearedDate, _deliveredDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))
)
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper Iaz38 You're right my count was off and this is working to calculate the days between the dates positively.
So is there no way to determine the date difference with the negative number using this formula as the DATEDIFF does?
The issue is I have another measure that counts anything in "Days Between" less than 2 as on time. So where as the example in this screenshot would be considered on time, if I use the newly generated "Days Between" that excludes weekends since it's a positive 10 it would look like it's actually 10 days rather than being early (if that makes sense).
Maybe that step is something I can tinker around with to fullfill what I need to show.. I grealty appreciatie all of your assistance!