This is a must-read article for anyone struggling with dates and multiple dates:
There is a overview page linking to these and other material on Community here:
You may try following. This is I have been using for my applications.
LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -2)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));
CALENDAR_DATE AS %KEY_CALENDAR_DATE,
DATE(CALENDAR_DATE) AS Date,
YEAR(CALENDAR_DATE) AS Year,
MonthName(date(CALENDAR_DATE)) as MonthName,
MONTH(CALENDAR_DATE) as MonthShort,
DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS Month,
NUM(MONTH(CALENDAR_DATE)) AS Month_num,
NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS YYYYMM,
// DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS YYYYMMDD,
DAY(CALENDAR_DATE) AS Day,
// DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS Weekday,
'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS Quarter,
// DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
// NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
// DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00') AS WeekC
($(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);
Your Data Model is bit messy.
Which is the fact table in all those tables? Seem like they all are Dim Tables.
Site_ID seem to be a field which can be key to other Dim Tables.
If the no of records are not many and if fields have same data then join/concatenate them to create a Fact Table.
Then Select a Date field from Fact Table to make a Key to your Master Table.
If not confidential and if possible can you also attach the xl files here?
Anyway I've inserted the Master Calendar script in attached file. But to use that you need to join it with a Date Key or DateID.
I suggest create a Fact, join it to Master Calendar based upon/selecting one date as DateID and use other Dim tables to key it with Fact.