Building relationship between several FACT tables and date table

[adell301]

I have 4 fact tables.

1. Accounting actuals

2. Budget

3. Outlook

4. Previous Outlook

The only fact table that has a defined date is accounting actuals, the rest only show data by month (name and #), there is no year, day or date. I am having trouble building a relationship as I want to compare tables (1vs2, 1vs3 and 3vs4). I want to add a slicer by month, but it only slices the accounting actuals data. I created a date table that has Date, days in month, start of month, end of month, month name, month #, Year and YYYYMM. How can I use the date table to slice fact tables 2-4 when Period name or number is not unique as it wont let me create a many to many relationship as it will create ambiguity between the other relationships I have.

[Ashish_Mathur · Accepted solution]

Hi,

In the Accounting Actuals table, create a calculated column to extract Month name.  Do not create any relationships between the 4 tables.  Create a Month table with 12 rows.  Create a relationship (Many to One and Single) from the Month name column of each of the 4 fact tables to the Month table.

Hope this helps.