Creating relationships between weeks, years, and a date table

[kylerwimbush]

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 column that has the values 2021 and 2022. This table is unique. 

I have a date table which has dates from 1/1/2021 to Today(Date). Then I have columns that extract the weeks(Week) and years(Year). This table is not unique. 

This is my relationship, I know its not correct that why I am here. 

What I have in my report is a date slicer where you can select two dates and display weeks worth of data from my data tables (DSP Overview and Mentor2). Those tables both have only weeks and years, no dates. 

My problem is that I cannot build the relationship properly so that I can use the date slicer to filter the correct weeks out. When ever I try I get errors in my relationship that say I try to introduce ambiguity or that I can't connect both the year column from the year table and the week column from the week table to one data table. Also another error I get when I try to add a both cross filtering path on both the week and year column, only one filtering path between tables in a data model is allowed. Any help? 

[kylerwimbush · Accepted solution]

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]

Hi,

I would suggest to create a Date_Table as per Best Practice. I will leave you the code you can paste into a blank query to invoce the Function:

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

let

DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

  InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), Int64.Type),

  InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), Int64.Type),

  InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),

  InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], Int64.Type),

  InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), Int64.Type),

  InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", null), type text),

  InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", null), type text),

  InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),

  InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], Int64.Type),

  InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), Int64.Type),

  InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),

  InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], Int64.Type),

  InsertWeekStarting = Table.AddColumn(InsertCalendarWkOrder, "Week Starting", each Date.StartOfWeek([Date]), type date),

  InsertWeekEnding = Table.AddColumn(InsertWeekStarting, "Week Ending", each Date.EndOfWeek([Date]), type date),

  InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), Int64.Type),

  InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], Int64.Type),

  InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, Int64.Type),

  InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", null), type text),

  InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 6 then 1 else if [Day Num Week] = 7 then 1 else 0, Int64.Type),

  InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd", null), type text),

  InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1, Int64.Type),

  InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

  InsertYesterday = Table.AddColumn(InsertDayOfYear, "Yesterday?", each if Date.DayOfWeek(DateTime.Date(Date.AddDays(DateTime.LocalNow(), +1))) = 1 and [Date] = DateTime.Date(Date.AddDays(DateTime.LocalNow(), -3)) then 1 else if Date.DayOfWeek(DateTime.Date(Date.AddDays(DateTime.LocalNow(), +1))) <> 1 and [Date] = DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1)) then 1 else 0, Int64.Type),

  InsertL7Days = Table.AddColumn(InsertYesterday, "L7Days?", each if [Date] >= DateTime.Date(Date.AddDays(DateTime.LocalNow(), -7)) then 1 else 0, Int64.Type),

  InsertCurrentDay = Table.AddColumn(InsertL7Days, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),

  InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),

  InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),

  InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),

  InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),

  InsertL12M = Table.AddColumn(InsertCurrentYear, "L12M?", each Date.IsInPreviousNDays([Date], 365), type logical),

  InsertCompletedDay = Table.AddColumn(InsertL12M, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then 1 else 0, Int64.Type),

  InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then 1 else 0, Int64.Type),

  InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then 1 else 0, Int64.Type),

  InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then 0 else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then 1 else 0, Int64.Type),

  InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then 1 else 0, Int64.Type),

  InsertYearsFromActualYear = Table.AddColumn(InsertCompletedYear, "YearsFromActualYear", each [Year] - Date.Year(DateTime.LocalNow()), Int64.Type),

  InsertMonthsFromActualMonth = Table.AddColumn(InsertYearsFromActualYear, "MonthsFromActualMonth", each if [Year]=Date.Year(DateTime.LocalNow()) then [Month Num]-Date.Month(DateTime.LocalNow())

else

[Month Num]-Date.Month(DateTime.LocalNow())+([YearsFromActualYear]*12), Int64.Type),

  InsertDaysFromToday = Table.AddColumn(InsertMonthsFromActualMonth, "DaysFromToday", each Duration.Days(Duration.From([Date]-DateTime.Date(Date.AddDays(DateTime.LocalNow(), 0)))), Int64.Type),

  InsertFiscalMonthNum = Table.AddColumn(InsertDaysFromToday, "Fiscal Month Num", each if [Month Num] > 4 then [Month Num]-4 else [Month Num]+8, Int64.Type),

  InsertFiscalQuarterNum = Table.AddColumn(InsertFiscalMonthNum, "Fiscal Quarter Num", each Number.RoundDown(([Fiscal Month Num]+2)/3), Int64.Type),

  InsertFiscalYear = Table.AddColumn(InsertFiscalQuarterNum, "Fiscal Year", each if [Month Num] <= 4 then Text.From([Year]-1)&"/"&Text.From([Year]-2000) else Text.From([Year])&"/"&Text.From([Year]-2000+1), type text),

  InsertFiscalYearNum = Table.AddColumn(InsertFiscalYear, "Fiscal Year Num", each Text.BeforeDelimiter([Fiscal Year], "/"), Int64.Type),

  #"InsertFiscalMM-YYYY" = Table.AddColumn(InsertFiscalYearNum, "Fiscal MM-YYYY", each Text.Combine({Text.PadStart(Text.From([Fiscal Month Num], "en-GB"), 2, "0"), "-", Text.From([Year], "en-GB")}), type text),

  #"InsertFiscalMM-YYYYOrder" = Table.AddColumn(#"InsertFiscalMM-YYYY", "Fiscal YYYY-MM Order", each [Year]*100+[Fiscal Month Num], Int64.Type),

  InsertFiscalYearsFromToday = Table.AddColumn(#"InsertFiscalMM-YYYYOrder", "FiscalYearsFromToday", each if Date.Month(DateTime.LocalNow()) <= 3 then Number.FromText([Fiscal Year Num])- Date.Year(DateTime.LocalNow()) - 1 else Number.FromText([Fiscal Year Num]) - Date.Year(DateTime.LocalNow()), Int64.Type),

  InsertCurrentFiscalMonthPeriod = Table.AddColumn(InsertFiscalYearsFromToday, "CurrentFiscalMonthPeriod", each if[Fiscal Month Num] < (if Date.Month(DateTime.LocalNow()) < 4 then Date.Month(DateTime.LocalNow()) + 9 else Date.Month(DateTime.LocalNow()) - 3) then 1 else 0, Int64.Type),

  #"Changed column type" = Table.TransformColumnTypes(InsertCurrentFiscalMonthPeriod, {{"Current Day?", Int64.Type}, {"Current Week?", Int64.Type}, {"Current Month?", Int64.Type}, {"Current Quarter?", Int64.Type}, {"Current Year?", Int64.Type}, {"L12M?", Int64.Type}})

in

  #"Changed column type"

in

CreateDateTable

[kylerwimbush]

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

[Uspace87]

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]

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]

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

)