Joining Date Table to Table with Multiple Date Field

[SMBM]

I'm hoping to gain some insight on how/when to deploy a dedicated date table when the tables to join to have multiple date columns.

Here are two scenarios that I think adequately summarize my use cases.  For context, I have been able to create a date table that has all dates in a range that I am concerned with along with determining our Fiscal Year and our Business Days.

1.  I have a dimension table with multiple date columns in it.  Currently, PBI has automatically added Date/Time Intelligence to all of them.  These columns include Date_Added, First_Inventory_Date, and Date_of_Obosolesence among others (~8 total).  We often have to identify skus that meet multiple criteria among those date columns.  How would I go about adding a date table to each of the columns on this dimensions table?  Would adding a date table and removing the date hierarchies from the dimensions table reduce overall size and/or speed up the refresh?  

2.  I have another dataset that includes a fact table that, again, has multiple date columns.  An example is for Purchase Orders where I have a Order_Entry_Date, Shipped_Date, and Date_of_Receipt.  What is the best method for adding a date table to this table?  I've read one possibility being that I create multiple date tables to connect to the fact table allowing for an active relationship on each one.  Is this the preferred method?

Thank you for any insight.  I may follow up on any responses with clarification or additional context.

[MattAllington · Accepted solution]

I wrote this article that covers most of your questions https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

as a general rule, I prefer to turn off the auto generated PBI date tables. My view is these are for beginners to make it easy to get started (JMO). It may or may not change the total size and refresh up or down. Chances are you won't need a calendar table for all date columns, so net net, it's probably better to do it yourself. 

another approach is to create a new fact table containing all your dates in the format

foreign key;

date type;

date;

set the relationship back to the dim/fact table as bidirectional cross filtering.

you can then join the new table of dates to a single calendar table. 

as Marco Russo always says, "it depends". 

[ProudlyAnalytic]

Hi, Matt

Was your article removed? Your link just takes me to your company's landing page.

Thanks for your time

[MattAllington]

I'm not sure the issue, but the article is still there

https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

if the link doesn't work, you can search for multiple relationships at the site

[ProudlyAnalytic]

Thanks for the quick response! Have a nice weekend!

[SMBM]

This is great information and a very informative article.  I'll read through it a couple of more times and consider how to best set up our data based off of these suggestions.