Welcome to the forum – great to have you here!
I would suggest abandoning the route you’re on and taking a completely different path, since the way you’re trying to connect your date and fact tables is going to make doing the type of time intelligence analysis you want to do difficult to impossible. Instead, I would recommend:
- Creating a proper date table at the daily level. You can go with the standard date table, or if you want to add some additional enhancements that will make time intelligence analyses even easier, go with the extended date table. References to both provided below:
- Because you’ve got a granularity mismatch (i.e., dates at the daily level, sales at the weekly and monthly level) you can’t use the traditional one-to-many physical relationship. So instead of using a many to many relationship (which is chockablock with problems), create a virtual relationship between your date table and your fact table at the matching granularity level (weekly or monthly or even yearly, depending on the needs of your analysis) using the TREATAS function, which will allow you to do proper time intelligence.
Here’s a great explanation of TREATAS:
I hope this is helpful.
- Brian