Week dimension in case of month split

Question

Hello!

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 00:00:00.000 vko.9 9

2024-02-27 00:00:00.000 2024-02-01 00:00:00.000 2 2024-02-26 00:00:00.000 vko.9 9

2024-02-28 00:00:00.000 2024-02-01 00:00:00.000 2 2024-02-26 00:00:00.000 vko.9 9

2024-02-29 00:00:00.000 2024-02-01 00:00:00.000 2 2024-02-26 00:00:00.000 vko.9 9

2024-03-01 00:00:00.000 2024-03-01 00:00:00.000 3 2024-02-25 00:00:00.000 vko.9 9

2024-03-02 00:00:00.000 2024-03-01 00:00:00.000 3 2024-02-25 00:00:00.000 vko.9 9

2024-03-03 00:00:00.000 2024-03-01 00:00:00.000 3 2024-02-25 00:00:00.000 vko.9 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 Hierarhcy shows week 9 only once

Regards

Cazzy

Answer 1

However If I add Month of year to attribute relationships week 9 splits into two nodes in hierarchy.

It works, if you define 2 parallel attribute relations like in the image below.

Only for ISO week you may have (sometimes) a problem with week 52; it can be related to two years.

Answer 2

Thank you for answer.

Solution for this was that for some reason there was error in generated contents of time table. Like show above whole week should have same week start date ( 00:00:00.000 2 2024-02-26). Now march dates had incorrect value 00:00:00.000 3 2024-02-25

Regards

Cazzy