Calculating the Transit time between two dates excluding holidays and situational dates

[zaidmasad]

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

[v-yuezhe-msft]

@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,
Lydia

[zaidmasad]

hi @v-yuezhe-msft,

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:

[v-yuezhe-msft]

@zaidmasad,

I send you a Private message, please check it.

Regards,
Lydia

[zaidmasad · Accepted solution]

Hello @v-yuezhe-msft

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

[v-yuezhe-msft]

@zaidmasad,

I note that you create another thread about the new issue, please use ISBLANK() function as suggested by v-chuncz-msft in that thread.

http://community.powerbi.com/t5/Desktop/Date-Difference-with-blank-values/m-p/258452#M115323

Regards,
Lydia

[zaidmasad]

@v-yuezhe-msft thanks for the reply, I have made the changes on the function but still not working, when I export the data from excel and refresh the table i get this error: 

When I write the code it gives me no error, just when I refresh the data i get the above errer. The new code is as below:

T1 (Response Time) = IF ( ISBLANK ( [Request Date] ) || ISBLANK ( [CollectionInProgressDate_UTC] || Dates[Request Date]> 'Dates'[CollectionInProgressDate_UTC]), BLANK (), var calendarTable= CALENDAR([Request Date],[CollectionInProgressDate_UTC])
var exceptDate=CALCULATETABLE(VALUES('Situational Holidays'[situationDate]),FILTER(ALL('Situational Holidays'),[Entity]=EARLIER('Dates'[OriginEntity])))
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)

[v-yuezhe-msft]

@zaidmasad,

The above error indicates that there is an issue in the Dates table, do you use calendar function to create the Dates table? Have you checked that if the start date or end date in the calendar function has blank value?

Regards,
Lydia

[zaidmasad]

@v-yuezhe-msft no the calendar table doest contain any blank values.

The start and end data are being exported from an excel sheet, when I remove the blanks from the excel sheet before exporting it, I dont get any error. This error appears just when the excel sheet have blank fields.

[v-yuezhe-msft]

@zaidmasad ,

It seems that you have solved this issue, right? If so, please mark appropriate reply as answer to close this thread, if not, please share the source file for us to analyze. 

Regards,
Lydia

[zaidmasad]

@v-huizhn-msft @MattAllington @Datatouille @MarcelBeug Appreciate if you can help me with this please.

[zaidmasad]

@v-ljerr-msft appreciate if you can help on this