Appologies, by mistake mark my question as Assumed Answered. As advised, I tried to create master calendar but Calender is not linked to any tables. What I need/expecting, bsed on Year/Month/Week selection from Master Calender, can able to generate reports for Activities/Contracts/Projects. Common calender should be used as selection/filter acroos all tables.
Activity.Updated_date = Calendar.Date
Contract.Sale_date = Calendar.Date
Project.Project_Start_date = Calendar.Date
If I use DERIVE FIELDS FROM SALE_DATE,UPDATED_DATE, PROJECT_START_DATE USING Calendar, then
Results are specific to appropriate tables. My requirements is, in one sheet, I have reports belongs to Activity, Contract, Project, all reports should affect according based on Master Calendar item.
Can you please advise?
I believe your issue is due to the fact that your DateLink table is only linked by CustomerNumber to the other tables.
First, I would suggest to step back and evaluate if a common date field for all three date fields is really what you want.
You can try to build a concatenated table consisting of most of the fields of the three tables (some can probably transferred to dimension tables), but this might imply other issues, when you select on specific fields values, thus excluding other rows from your aggregations.