Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
tsuggs1
Helper I
Helper I

Trying to find a way to exclude weekends using DateDiff with dates from two tables

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!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@tsuggs1 

 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

30 REPLIES 30
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38,

 

Not sure what I'm doing wrong, but it's throwing an error on everything past 'FORMAT'.

 

See attached screenshot.Screen Shot 2020-01-09 at 2.51.24 PM.png

az38
Community Champion
Community Champion

@tsuggs1 

replace ";" to comma ","

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Okay, here's what I am getting now:

 

Screen Shot 2020-01-09 at 2.56.48 PM.png

az38
Community Champion
Community Champion

@tsuggs1 

close parenthesys ")" before ", FORMAT([Date],"w")=1". it should be "...Cleared])), FORMAT..."

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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.

 

Screen Shot 2020-01-09 at 3.07.31 PM.png

az38
Community Champion
Community Champion

@tsuggs1 

are there any cases when cleared before delivered?

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Yes there is, in my other DAYSBETWEEN calculated column it just counts it as a negative number.

az38
Community Champion
Community Champion

@tsuggs1 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

az38
Community Champion
Community Champion

@tsuggs1 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

@tsuggs1 

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

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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?

 

Screen Shot 2020-01-09 at 3.31.30 PM.png

az38
Community Champion
Community Champion

@tsuggs1 

yes, it either blank value or it cannot find RELATED value. check your data

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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
Community Champion
Community Champion

@tsuggs1
Try .[Date] after columns name 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Unfortantely no luck.. I made sure all the empty date fields in each column were removed as well.

 

Still a mystery. 

az38
Community Champion
Community Champion

@tsuggs1
In what table do you add a column?
It’s so much possible that the problem is in related. Could you create related columns, add it to visual and check if there are any empty relations? Or share your pbix file after deleting sensitive data

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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:

Screen Shot 2020-01-10 at 8.45.20 AM.png

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:

Screen Shot 2020-01-10 at 8.45.49 AM.png

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.

 

Screen Shot 2020-01-10 at 9.20.07 AM.png

 

Let me know if you need anything else and I will share as much as I can, hopefully this shows you enough.

az38
Community Champion
Community Champion

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

 

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.