Forum Discussion

kylerwimbush's avatar
kylerwimbush
Helper I
3 years ago
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...
Need Help
  • kylerwimbush's avatar
    kylerwimbush
    3 years ago

    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. 

Uspace87's avatar
Uspace87
Resolver III
3 years ago

I woukd Fix the Calendar table because the "Year" is already included in the calendar table so you don't need to have it as a Dimension, the same for week.

kylerwimbush's avatar
kylerwimbush
Helper I
3 years ago

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. 

 

  • Uspace87's avatar
    Uspace87
    Resolver III
    3 years ago

    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])
       
    )