Forum Discussion
Solved
Creating relationships between weeks, years, and a date table
I am trying to make my relationships work for my report. I have a weeks table which has a week column which is just numbered 1-53. This table is unique. I have a year table with a year colu...
Made a 'Date" Column in my Data Tables that uses the week and year number to get the date of the start of that week.
Date.StartOfWeek(Date.AddWeeks(#date([Year], 1, 1), [Week]-1), Day.Monday)
Then I linked the date column in my calendar table to the date column in my data tables.
Then I wouldn't be able to connect the Values I have in my Year and Week tables to the calendar table.
I also can't directly connect my calendar table to my data tables because that would introduce a many to many relationship which wouldn't work.
You Can Create a new table passing this code:
Calendar = ADDCOLUMNS(
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"WeekNumber" , WEEKNUM([Date])
)