Forum Discussion
Calculating the Transit time between two dates excluding holidays and situational dates
Hello,
I have a set of data, and i want to calculate the transit time excluding the situational dates and holidays for the origin. I have made three tables one for the situational dates, one for the holidays and a calendar table as below:
Situational Dates:
Weekends:
Calendar:
I have this code, but it doesnt take into consideration what days for each origin are the weekends:
Detail = var calendarTable= CALENDAR([Start Date],[End Date]) var exceptDate=CALCULATETABLE(VALUES(Sheet3[situation Date]),FILTER(ALL(Sheet3),[Entity]=EARLIER(Sheet2[Origin Entity])||[Entity]=EARLIER(Sheet2[Destination Entity]))) var filtered=ADDCOLUMNS(EXCEPT(calendarTable,exceptDate),"Day Of Week",WEEKDAY([Date],1)) return CONCATENATEX(FILTER(filtered,[Day Of Week]<>1&&[Day Of Week]<>7),[Date],",") Count = var calendarTable= CALENDAR([Start Date],[End Date]) var exceptDate=CALCULATETABLE(VALUES(Sheet3[situation Date]),FILTER(ALL(Sheet3),[Entity]=EARLIER(Sheet2[Origin Entity])||[Entity]=EARLIER(Sheet2[Destination Entity]))) var filtered=ADDCOLUMNS(EXCEPT(calendarTable,exceptDate),"Day Of Week",WEEKDAY([Date],1)) return COUNTROWS(FILTER(filtered,[Day Of Week]<>1&&[Day Of Week]<>7))
My data looks like the below:
| AWB | Origin Entity | Destination Entity | Start Date | End Date |
| IMAE10002 | RUH | DXB | 9/17/2017 | 9/17/2017 |
| IMAE10006 | LON | DXB | 7/3/2017 | 7/3/2017 |
| IMAE10018 | MIL | DXB | 7/3/2017 | 7/4/2017 |
| IMAE10027 | LON | DXB | 9/18/2017 | 9/19/2017 |
| IMAE10028 | HAX | DXB | 9/18/2017 | 9/19/2017 |
Thank you
Hello Anonymous
I have made some changes on the code now and its working well. but I am facing a problem now when one of the dates is blank or the start date is after the end date.
Can this problem be fixed?
Thank you
The new code is as below:
T1 = var calendarTable= CALENDAR([CreatedOn],[CollectionInProgressDate_UTC]) var exceptDate=CALCULATETABLE(VALUES('Situational Holidays'[situationDate]),FILTER(ALL('Situational Holidays'),[Entity]=EARLIER('Dates'[ShipperEntity]))) var filtered=ADDCOLUMNS(EXCEPT(calendarTable,exceptDate),"Day Of Week",WEEKDAY([Date],1)) return COUNTROWS(FILTER(filtered,[Day Of Week]<> 'Dates'[First weekend] && [Day Of Week]<>'Dates'[secound weekend]))-1
11 Replies
v-ljerr-msft appreciate if you can help on this
v-huizhn-msft MattAllington Datatouille MarcelBeug Appreciate if you can help me with this please.
zaidmasad,
In your Weekends table, do you mean that for entity ACC, the weekends are Sunday and Monday? If so, what are the weekends for the Origin Entity and Destination Entity(RUH,LON, MIL,LON,HAX,DXB)? And do you want to exclude weekends for Origin Entity or Destination Entity?
We will appreciate that if you can share the complete data of these tables. You can upload the excel file to OneDrive, and post shared link of the excel file here.
Regards,
Lydiahi Anonymous,
Yes, for example in DXB the weekends are friday and saturday, for LON its Saturday and Sunday.
The weekends should be excluded based on the origin entity. it is restricted to open Onedrive in our company so he weekends for the requested entities are as below:
Entity 1st weekend 2nd weekend
MIL 6 0 LON 6 0 DXB 5 6 RUH 5 6 HAX 5 6 The tables I have and their fields are as the following:
zaidmasad,
I send you a Private message, please check it.
Regards,
Lydia
