For example In year 2024 week 9 splits into two months
| PK_Date | Month | Month_Of_Year | Week | Week_Name | Week_Of_Year | |
|---|---|---|---|---|---|---|
| 2024-02-26 | 00:00:00.000 | 2024-02-01 | 00:00:00.000 | 2 | 2024-02-26 | vko.9 |
| 2024-02-27 | 00:00:00.000 | 2024-02-01 | 00:00:00.000 | 2 | 2024-02-26 | vko.9 |
| 2024-02-28 | 00:00:00.000 | 2024-02-01 | 00:00:00.000 | 2 | 2024-02-26 | vko.9 |
| 2024-02-29 | 00:00:00.000 | 2024-02-01 | 00:00:00.000 | 2 | 2024-02-26 | vko.9 |
| 2024-03-01 | 00:00:00.000 | 2024-03-01 | 00:00:00.000 | 3 | 2024-02-25 | vko.9 |
| 2024-03-02 | 00:00:00.000 | 2024-03-01 | 00:00:00.000 | 3 | 2024-02-25 | vko.9 |
| 2024-03-03 | 00:00:00.000 | 2024-03-01 | 00:00:00.000 | 3 | 2024-02-25 | vko.9 |
If I make date dimension with attribute relationships
PK Date -> Week Of Year - Year
Hierarchy shows week 9 only once (just like customer wants)
However If I add Month of year to attribute relationships week 9 splits into two nodes in hierarchy.
Customer wants to use month as a filter. How to solve this so hierarchy shows week 9 only once