There are quite a few "DimDate"/"Calendar" table generation patterns out there now - e.g. http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/ It seems you really need one to may full use of the DAX Time Intelligence functions.
The function script below cloned from that link above adds a "Week Starting" column as well as you seem to be using that. Cut out what you don't need or add other custom data dimensions - e.g. see https://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/ for Fiscal Years etc.
//Create Date Dimension
(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),
Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, "Year",
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , "Quarter",
each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , "Week Number",
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, "Month Number",
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Month",
each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , "Day of Week",
each Date.ToText([Date],"dddd")),
//Add Week Starting Date (N.B. Need to force (default!) Sunday start of week for some reason)
WeekStarting = Table.AddColumn(DayOfWeek, "Week Starting",
each Date.StartOfWeek([Date], Day.Sunday), type date),
//Add Week Ending Date (N.B. Need to force (default!) Sunday start of week for some reason)
WeekEnding = Table.AddColumn(WeekStarting, "Week Ending",
each Date.EndOfWeek([Date], Day.Sunday), type date)
in
WeekEndingSave it as a Query named "CreateCalendarTable" or similar. Then call that function from a SECOND query called "DimDate" or "Calendar" etc. which gives you the populated table you need. For simplicity in this example, I've restricted my Calendar (per red below) to a week either side of the Min and Max vales for Date in the Data table "Table1", but you can adapt those to "Today plus a Year", a fixed date #date(2000, 1, 1), etc.
let
Source = CreateCalendarTable(Date.AddDays(List.Min(Table1[Date]),-7), Date.AddDays(List.Max(Table1[Date]), 7)),
ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Week Number", Int64.Type}, {"Month Number", Int64.Type}}),
SortedRows = Table.Sort(ChangedTypes,{{"Date", Order.Ascending}})
in
SortedRowsThen create a "Many to One (*:1)" relationship from Table1 to Calendar tables on the Date column. My test version uses a Cross Filter Direction of Both, but Single should also work and may be forced on you by your data model in some cases.
See how that goes.
Cheers.
