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. 

kylerwimbush's avatar
kylerwimbush
Helper I
3 years ago

Sorry, I have a date table I forgot to put it in the post. 

 

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