Forum Discussion

lefinalzugzwang's avatar
lefinalzugzwang
Frequent Visitor
9 years ago
Solved

Implementing a date table without interfering with existing relationships

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...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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