Forum Discussion
Model Design and Dates
You would add a date field to your fact table that is the first day of the month for the year / month. For example, if your year month is 2023 October, add a date field to the fact table that is 10/1/2023. Then you can link that to your date table.
Thank you - yes this would work.
I do have one issue that would cause a problem with this though.
The data is set up in date periods in the same column so it contains both week and month data. The date period key is 201801W01 or 201901M01. W01 is week 1 of the year and M01 is first month of the year. I can do the calculation easily with the month, but I don't know how I would achieve this with the week number. I have split the month data and week data into different tables in the model so the month ones are working fine now and linking to the date table. I don't know how to achieve the same for the week column
