Calculating transit time excluding situational dates and holidays for origin and destination

[zaidmasad]

[zaidmasad]

@v-shex-msft I can share with you the file but I dont know how actually.

[v-shex-msft]

HI @zaidmasad,

You can use below formula to calculate the transit time except the holiday.

Count = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([Start Date],[End Date]),"Day Of Week",WEEKDAY([Date],1)),[Day Of Week]<>1&&[Day Of Week]<>7)) 

In addition, I'm not so sure how to use the situational date table, can you share some detail information?

Regards,

Xianxin Sheng

[zaidmasad]

@v-shex-msft Also some of the countries have weekend as Saturday and Sunday, the other have weekends as Friday and Saturday. Can this be included in the calculation? Thank you very much for your help

[v-shex-msft · Accepted solution]

Hi @zaidmasad,

You can take a look a below formula which used to get transmit date range(except the situational dates)

Calculated column: get date range except specify date and normal holiday(saturday and sunday)

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))

>>Also some of the countries have weekend as Saturday and Sunday, the other have weekends as Friday and Saturday.

You need to create a table to store these specific day of week, then excluded from current date range.

Regards,

Xiaoxin Sheng

[zaidmasad]

@v-shex-msft Hello again,

I still have a problem with calculating the transit time, now the formula to exclude situational dates is working well. but I need a formula to exclude holidays based on the country.

I have a table for holildays that looks as below for the entities regardless if it is a destination or origin:

What should the new formula for calculating the tranist time be?

Thank you very much for your help.

[zaidmasad]

Hi @v-shex-msft,

Can you please explain me exactly how does this function work? I am new to these kind of function and powerBI.

Thank you very much

[v-shex-msft]

Hi @zaidmasad,

Some comment of the formula:

Detail = 
var calendarTable= CALENDAR([Start Date],[End Date]) //use "start date" and "end date" from current row content to create a calendar table.
var exceptDate=CALCULATETABLE(VALUES(Sheet3[situation Date]),FILTER(ALL(Sheet3),[Entity]=EARLIER(Sheet2[Origin Entity])||[Entity]=EARLIER(Sheet2[Destination Entity]))) //use current "origin entity" and "destination entity" to filter situation date from situation table.
var filtered=ADDCOLUMNS(EXCEPT(calendarTable,exceptDate),"Day Of Week",WEEKDAY([Date],1)) // add "day of week" column to calendar table(it used to filter normal holiday)
return
CONCATENATEX(FILTER(filtered,[Day Of Week]<>1&&[Day Of Week]<>7),[Date],",") // filter date and merge them to text

COUNTROWS(FILTER(filtered,[Day Of Week]<>1&&[Day Of Week]<>7)) //filter date and get count of days.

BTW, you can also refer to below link which about dax functions:

DAX Function Reference

Regards,

Xiaoxin Sheng 

[zaidmasad]

@v-shex-msft