Forum Discussion
Model Design and Dates
I would add a table to your model that you could use as a lookup. It would contain the YMW values and the date you want associted to it. You would link it to your fact table that has the YMW and pull over the date field then link the date field to the Dates table.
| YMW | Date |
| 2022M01W01 | 1/1/2022 |
| 2022M01W02 | 1/2/2022 |
| 2022M01W03 | 1/9/2022 |
| 2022M01W04 | 1/16/2022 |
| 2022M01W05 | 1/23/2022 |
| 2022M01W06 | 1/30/2022 |
| 2022M02W06 | 2/1/2022 |
| 2022M02W07 | 2/6/2022 |
| 2022M02W08 | 2/13/2022 |
| 2022M02W09 | 2/20/2022 |
| 2022M02W10 | 2/27/2022 |
| 2022M03W10 | 3/1/2022 |
| 2022M03W11 | 3/6/2022 |
| 2022M03W12 | 3/13/2022 |
| 2022M03W13 | 3/20/2022 |
| 2022M03W14 | 3/27/2022 |
| 2022M04W14 | 4/1/2022 |
| 2022M04W15 | 4/3/2022 |
| 2022M04W16 | 4/10/2022 |
| 2022M04W17 | 4/17/2022 |
| 2022M04W18 | 4/24/2022 |
| 2022M05W19 | 5/1/2022 |
| 2022M05W20 | 5/8/2022 |
| 2022M05W21 | 5/15/2022 |
| 2022M05W22 | 5/22/2022 |
| 2022M05W23 | 5/29/2022 |
| 2022M06W23 | 6/1/2022 |
| 2022M06W24 | 6/5/2022 |
| 2022M06W25 | 6/12/2022 |
| 2022M06W26 | 6/19/2022 |
| 2022M06W27 | 6/26/2022 |
| 2022M07W27 | 7/1/2022 |
| 2022M07W28 | 7/3/2022 |
| 2022M07W29 | 7/10/2022 |
| 2022M07W30 | 7/17/2022 |
| 2022M07W31 | 7/24/2022 |
| 2022M07W32 | 7/31/2022 |
| 2022M08W32 | 8/1/2022 |
| 2022M08W33 | 8/7/2022 |
| 2022M08W34 | 8/14/2022 |
| 2022M08W35 | 8/21/2022 |
| 2022M08W36 | 8/28/2022 |
| 2022M09W36 | 9/1/2022 |
| 2022M09W37 | 9/4/2022 |
| 2022M09W38 | 9/11/2022 |
| 2022M09W39 | 9/18/2022 |
| 2022M09W40 | 9/25/2022 |
| 2022M10W40 | 10/1/2022 |
| 2022M10W41 | 10/2/2022 |
| 2022M10W42 | 10/9/2022 |
| 2022M10W43 | 10/16/2022 |
| 2022M10W44 | 10/23/2022 |
| 2022M10W45 | 10/30/2022 |
| 2022M11W45 | 11/1/2022 |
| 2022M11W46 | 11/6/2022 |
| 2022M11W47 | 11/13/2022 |
| 2022M11W48 | 11/20/2022 |
| 2022M11W49 | 11/27/2022 |
| 2022M12W49 | 12/1/2022 |
| 2022M12W50 | 12/4/2022 |
| 2022M12W51 | 12/11/2022 |
| 2022M12W52 | 12/18/2022 |
| 2022M12W53 | 12/25/2022 |
I agree with jdbuchanan71 . However, I think you could also just do this with one date table. You could create a calculated column in your date dimension to reproduce the format of your period key in your fact table by using combinations of functions like YEAR, MONTH, WEEKNUM, and FORMAT. Then join the two on that key.
If you did that, add the YMW column to your date table, your join to your fact table would be many to many unless I am not understanding your post.
Oh jeez. You are 100% correct. I completely overlooked that. Good call! Thank you 🙂
