Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I am hoping to find some clear advice on a way to find the number of days between two dates and exclude weekends.
I have tried some solutions I have seen where people have asked similar questions about excluding weekends or holidays, however my issue is somewhat unique as my dates are in two separate tables. I have Table 1 that contains a date column called 'ActualTime - CustomsCleared' and another table called Table 2 that contains a date column called 'ActualTime - Arrived'. I was able to create a relationship between the tables and use 'DateDiff' to calculate the number of days between with no issue.
I am struggling to find a way use this method with DATEDIFF that exlcludes weekends. Any help or advice is greatly appreciated!
Solved! Go to Solution.
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
Hi @tsuggs1
try this technique. create a calculated column
Column = datediff([StartDate];[EndDate];day)-countrows(FILTER(calendar([StartDate];[EndDate]);FORMAT([Date];"w")="1" || FORMAT([Date];"w")="7"))+1
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @az38,
Not sure what I'm doing wrong, but it's throwing an error on everything past 'FORMAT'.
See attached screenshot.
Okay, here's what I am getting now:
@az38 Got it!
Looks like it's almost working, but it's giving me a strange message about the CALENDAR function. I tried swapping them around just to see if it would work and same result.
Yes there is, in my other DAYSBETWEEN calculated column it just counts it as a negative number.
for calendar function first date should be first.
could you copy-paste your code here, it's almost impossible to write it by myself 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
Haha understandable.
Column = 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)-countrows(FILTER(calendar(RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered]), RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))+1
I will try to repeat 🙂
Column =
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(
RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered]) < RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared]),
countrows(FILTER(calendar(RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered]), RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")),
countrows(FILTER(calendar(RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Customs Cleared]), RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Delivered])), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))
)
+1
do not hesitate to give a kudo to useful posts and mark solutions as solution
that would be much better
Column =
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
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
Okay so both those are now saying the 'the start date or end date in Calendar function can not be Blank value.. There are also some that are blank which I guess is why it's giving that error?
@az38 So I filtered out all null values from both date columns and still getting the same error. I don't see why it would be something in the RELATE function since everything works using:
Days Between = 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)
Any other advice? I really appreiciate your help!
@az38 Unfortantely no luck.. I made sure all the empty date fields in each column were removed as well.
Still a mystery.
@az38 - my data is connected to a database and I can't delete any data out of it so I can't share my .pbix file.
Here are some screenshots of the structure I have set up in Power BI.
My tables:
ot_milestone contains my date column 'ActualTime - Customs Cleared' and then ot_milestone(2) is a duplicated table that contains my date column 'ActualTime - Arrived'.
My relationships:
The last relationship between shipment_links and ot_milestone is probably the most important as my 'Days Between' column is in shipment_links table. The reason why it's here is because it associates the 'Days Between' to a shipment number that's in the shipment_links table. Here is my 'Days Between' column formula: "Days Between = 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)" and this works with no issue.
Let me know if you need anything else and I will share as much as I can, hopefully this shows you enough.
ok, @tsuggs1
lets try to debug.
add
column=RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered].[Date])
and
column2=RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared].[Date])
to your shipment_links table.
then go to the Data view (left pane of power bi window) and in table check there will not be any empty values in column and column2
do not hesitate to give a kudo to useful posts and mark solutions as solution
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |