I have a set of data for shipments, and I need to calculate the transit time but the holidays have to be excluded based on the origin and the destination.
My Shipments sheet look like this:
And I have another Calendar table and a table for the situational dates each with its entity:
Please advise how can i do such thing
Go to Solution.
First, you need to create a relationship between the shipment and situation tables.Then create two calculated columns using the formulas. You will get the ransit time excluding situational date.
situation = IF(ISBLANK(RELATED(situation[situationDate])),0, CALCULATE(COUNT(situation[situationDate]),FILTER(Shipments,Shipments[Start Date]<=RELATED(situation[situationDate])&&Shipments[End Date]>=RELATED(situation[situationDate]))))
transit time = DATEDIFF(Shipments[Start Date],Shipments[End Date],DAY)-Shipments[situation]
In addition, you want to exclude the holidays, what's the holiday, could you please share more details?Thanks,Angelia
View solution in original post
Do you need help in Spanish? Check out our new Spanish community section.