Implementing a date table without interfering with existing relationships

[lefinalzugzwang]

Hi all, 

I am trying to compare data from my Sales table, to data in my Forecasts table. I have two different date fields involved here. Forecast.Date which is the date that a particular sale from Forecasts is predicted to take place, and Sales.Date which is the actual date when a particular sale took place. Below is a screengrab of my schema.

As you would expect, everything revolves around products! So here's the question... Obviously there are many tables here which have a date field. As of yet I haven't been able to properly analyse disparate tables by date in a single visual. I know that I probably need a date table. But I don't fully understand how to create relationships between these dates/date table without disrupting the schema and hence other relationships.

For example, if I wanted to create a matrix with Product.Name as the rows, and then Months as columns, and as values have Forecast.Value and Sale.Value as my values. At the moment the date filtering only works for one set of these values. But when I try to implement the Date Table between forecast.date and sale.date I lose one of the relationships between these and the product table. 

I've tried to be as clear as possible, apologies if this doesn't come through!

L

[MattAllington]

Turn off the bidirectional filtering in your current relationships. Then you can add your date table without any problems. Send me a pm with your email address and I will send you a pre release of my blog article on this topic (not due for release until 14 Feb

[lefinalzugzwang]

Thanks a lot for this, it does sort of make sense that the relationships should be set up like this, and it's what I did. I am going to pm you once I figure out how to do so!

[v-shex-msft · Accepted solution]

Hi @lefinalzugzwang,

>>But I don't fully understand how to create relationships between these dates/date table without disrupting the schema and hence other relationships.

For the date table, I'd like to suggest you create with all tables date fields, for example:

Date Table = 
var temp=DISTINCT(UNION(VALUES(Table1[Date]),VALUES(Table2[Date]),VALUES(Table3[Date]),...))
Return
CALENDAR(MINX(temp,[Date]),MAXX(temp,[Date]))

Then you can create one to many relationship from calendar table to each other table which contains the date field, use calendar table as source of the filter.

Regards,

Xiaoxin Sheng