Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kylerwimbush
Helper I
Helper I

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. 

kylerwimbush_0-1668698619996.png

I have a year table with a year column that has the values 2021 and 2022. This table is unique. 

kylerwimbush_1-1668698743849.png

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. 

kylerwimbush_0-1668701096349.png

 

 

kylerwimbush_2-1668698940825.png

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? 

1 ACCEPTED SOLUTION
kylerwimbush
Helper I
Helper I

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. 

View solution in original post

6 REPLIES 6
kylerwimbush
Helper I
Helper I

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
Resolver III
Resolver III

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

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

 

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.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.