let CreateDateTable = (StartDate as date, EndDate as date, FirstMonthofFinYear as number, optional Culture as nullable text) as table => let DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1, 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", "CalendarDate"}}), InsertShiftedDate = Table.AddColumn(RenamedColumns, "ShiftedDate", each Date.AddMonths([CalendarDate],(13-FirstMonthofFinYear))), InsertCalendarYear = Table.AddColumn(InsertShiftedDate, "CalendarYear", each Date.Year([CalendarDate])), InsertDayShort = Table.AddColumn(InsertCalendarYear, "DayShort", each Date.ToText([CalendarDate], "ddd", Culture), type text), InsertDayLong = Table.AddColumn(InsertDayShort, "DayLong", each Date.ToText([CalendarDate], "dddd", Culture), type text), InsertDayInMonth = Table.AddColumn(InsertDayLong, "DayInMonth", each Date.Day([CalendarDate])), InsertMonthShort = Table.AddColumn(InsertDayInMonth, "MonthShort", each Date.ToText([CalendarDate], "MMM", Culture), type text), InsertMonthLong = Table.AddColumn(InsertMonthShort, "MonthLong", each Date.ToText([CalendarDate], "MMMM", Culture), type text), InsertMonthShortCaption = Table.AddColumn(InsertMonthLong, "MonthShortCaption", each (try(Text.Range([MonthShort],0,3)) otherwise [MonthName]) & "-" & Number.ToText([CalendarYear])), InsertMonthLongCaption = Table.AddColumn(InsertMonthShortCaption, "MonthLongCaption", each [MonthLong] & " " & Number.ToText([CalendarYear])), InsertCalendarMonthNo = Table.AddColumn(InsertMonthLongCaption, "CalendarMonthNo", each Date.Month([CalendarDate])), InsertCalendarMonth = Table.AddColumn(InsertCalendarMonthNo, "CalendarMonth", each [CalendarYear] * 100 + [CalendarMonthNo]), InsertCalendarQuarterNo = Table.AddColumn(InsertCalendarMonth, "CalendarQuarterNo", each Date.QuarterOfYear([CalendarDate])), InsertCalendarQuarter = Table.AddColumn(InsertCalendarQuarterNo, "CalendarQuarter", each [CalendarYear] * 100 + [CalendarQuarterNo]), InsertCalendarQuarterShort = Table.AddColumn(InsertCalendarQuarter, "CalendarQuarterShort", each "Q" & Number.ToText([CalendarQuarterNo])), InsertCalendarQuarterLong = Table.AddColumn(InsertCalendarQuarterShort, "CalendarQuarterLong", each "Quarter " & Number.ToText([CalendarQuarterNo])), InsertCalendarQuarterShortCaption = Table.AddColumn(InsertCalendarQuarterLong, "CalendarQuarterShortCaption", each Number.ToText([CalendarYear]) & " Q" & Number.ToText([CalendarQuarterNo])), InsertCalendarQuarterLongCaption = Table.AddColumn(InsertCalendarQuarterShortCaption, "CalendarQuarterLongCaption", each Number.ToText([CalendarYear]) & " Quarter " & Number.ToText([CalendarQuarterNo])), InsertFinancialYearNo = Table.AddColumn(InsertCalendarQuarterLongCaption, "FinancialYearNo", each Date.Year([ShiftedDate])), InsertFinancialMonthNo = Table.AddColumn(InsertFinancialYearNo, "FinancialMonthNo", each Date.Month([ShiftedDate])), InsertFinancialMonth = Table.AddColumn(InsertFinancialMonthNo, "FinancialMonth", each [FinancialYearNo] * 100 + [FinancialMonthNo]), InsertFinancialQuarterNo = Table.AddColumn(InsertFinancialMonth, "FinancialQuarterNo", each Date.QuarterOfYear([ShiftedDate])), InsertFinancialQuarter = Table.AddColumn(InsertFinancialQuarterNo, "FinancialQuarter", each [FinancialYearNo] * 100 + [FinancialQuarterNo]), InsertFinancialQuarterLongCaption = Table.AddColumn(InsertFinancialQuarter, "FinancialQuarterLongCaption", each "FQ " & Number.ToText([FinancialYearNo]) & " Q" & Number.ToText([FinancialQuarterNo])), InsertFinYrPt1 = Table.AddColumn(InsertFinancialQuarterLongCaption, "FinYrPt1", each if [CalendarYear] = [FinancialYearNo] then [CalendarYear]-1 else [CalendarYear]), InsertFinYrPt2 = Table.AddColumn(InsertFinYrPt1, "FinYrPt2", each if [CalendarYear] = [FinancialYearNo] then [CalendarYear] else [FinancialYearNo]), InsertFinancialYear = Table.AddColumn(InsertFinYrPt2, "FinancialYear", each "FY " & Number.ToText([FinYrPt1]) & "/" & Text.End(Number.ToText([FinYrPt2]),2)), InsertFinancialYearShortCaption = Table.AddColumn(InsertFinancialYear, "FinancialYearShortCaption", each "FY " & Text.End(Number.ToText([FinYrPt1]),2) & "/" & Text.End(Number.ToText([FinYrPt2]),2)), InsertFinancialYearLongCaption = Table.AddColumn(InsertFinancialYearShortCaption, "FinancialYearLongCaption", each "FY " & Number.ToText([FinYrPt1]) & "/" & Number.ToText([FinYrPt2])), InsertDayInWeekMonday = Table.AddColumn(InsertFinancialYearLongCaption, "DayInWeekMonday", each Date.DayOfWeek([CalendarDate])+1), InsertDayInWeekSaturday = Table.AddColumn(InsertDayInWeekMonday, "DayInWeekSaturday", each Date.DayOfWeek([CalendarDate],Day.Saturday)+1), InsertDayInWeekSunday = Table.AddColumn(InsertDayInWeekSaturday, "DayInWeekSunday", each Date.DayOfWeek([CalendarDate],Day.Sunday)+1), InsertThursdayOfCurrentWeek = Table.AddColumn(InsertDayInWeekSunday, "ThursdayOfCurrentWeek", each Date.AddDays([CalendarDate],4-(Date.DayOfWeek([CalendarDate])+1))), InsertWeekdayNumofYearStart = Table.AddColumn(InsertThursdayOfCurrentWeek,"WeekdayNumOfYearStart", each Date.DayOfWeek(Date.StartOfYear([ThursdayOfCurrentWeek]))+1), InsertFirstThursdayOfYear = Table.AddColumn(InsertWeekdayNumofYearStart , "FirstThursdayOfYear", each if [WeekdayNumOfYearStart] > 4 then Date.AddDays(Date.StartOfYear([ThursdayOfCurrentWeek]), (11- [WeekdayNumOfYearStart])) else Date.AddDays(Date.StartOfYear([ThursdayOfCurrentWeek]), (4 - [WeekdayNumOfYearStart]))), InsertCalendarWeek = Table.AddColumn(InsertFirstThursdayOfYear , "CalendarWeek",each if Date.DayOfYear([FirstThursdayOfYear]) <=4 then Date.WeekOfYear([ThursdayOfCurrentWeek]) else Date.WeekOfYear([ThursdayOfCurrentWeek]) - 1), InsertCalendarWeekCaption = Table.AddColumn(InsertCalendarWeek, "CalendarWeekCaption", each "W-" & Number.ToText([CalendarWeek])), InsertCalendarWeekKey = Table.AddColumn(InsertCalendarWeekCaption, "CalendarWeekKey", each Date.Year([ThursdayOfCurrentWeek]) * 100 + [CalendarWeek]), RemovedColumns = Table.RemoveColumns(InsertCalendarWeekKey , {"ShiftedDate", "FinYrPt1", "FinYrPt2","ThursdayOfCurrentWeek", "WeekdayNumOfYearStart" ,"FirstThursdayOfYear"}) in RemovedColumns in CreateDateTable