Exclude dates table Filter

[Snowy34]

Good day all, 

Here we are back with a new issue, what I have is two data tables, the first table holds my records of the deliveries from order date to delivered date. What I will like to add a column Days to Deliver that calculates the number of days it's taken to deliver an order.

Now, this is very easy to do but my issue is I got more than one warehouse in the report and each warehouse has its own days that need to be excluded and also excluded weekends. I have created a calculated column that returns of its a weekend or weekday so that part was simple.

So the below shows an order for WH222 has taken 2 days to delivery - the weekend, WH666 order has taken also 2days but that is because date 666 - 19/11/19 is excluded and so on. Think you get the picture.

So how could I create something that looks at the WH number first then looks at the excluded dates before it returns a result?

Order	Warehouse	Ordered	Delivered	Days to deliver
8518	222	15/11/19	19/11/19	2
8846	666	15/11/19	20/11/19	2
4412	888	14/11/19	19/11/19	3

exclude dates table 

WH-DATE

666 - 19/11/19

222 - 27/11/19

888 - 4/12/19

Regards 

Snowy

[Ashish_Mathur]

Hi,

Build a relationship from the Warehouse column of Table1 to the WH column of Table2.  In Table1, write this calculated column formula to bring over the Date from Table2 to Table1.

=RELATED('Table2'[Date])

Does this help?

[Snowy34]

Hi @Ashish_Mathur 

When I try and create the relationship I get this error "The cardinality you selected isn't valid for this relationship"

Regards 

[Ashish_Mathur · Accepted solution]

Hi,

Ensure that in the WH column of Table2, there are only unique values.  Also, in the Relatioinshop window, select Many to One and Singe.

[Snowy34]

Hi, @Ashish_Mathur  again,

But if I do that most of my data is now gone apart from one day... across all the warehouses

[Ashish_Mathur]

Hi,

I cannot understand what you are trying to tell me.

[Snowy34]

@Ashish_Mathur 

Basically I need to get a number of days it has taken to deliver an item but excluding weekends and public holidays, this would be quite easy to do if I didn't have warehouses that are in different states. Meaning what is a public holiday in Wearhouse 666 not a public holiday in Wearhouse 222.

I created a table that holds all public holidays for each Wearhouse and I was hoping I could use that when figuring out my days taken to deliver an order.

I hope this clears out what my issue is.

[Ashish_Mathur]

Hi,

What problem do you run into when you impleent my suggested solution.  Please be specific.

[Snowy34]

Good day @Ashish_Mathur 

Thank you for your help, in the end, I got it working using your approach 🙂 

Regards

Snowy

[Ashish_Mathur]

You are welcome.