DimDates

let // configurations start Today = Date.From(DateTime.LocalNow()), // today's date FromYear = 2010, // set the start year of the date dimension. dates start from 1st of January of this year ToYear = 2050, // set the end year of the date dimension. dates end at 31st of December of this year StartofFiscalYear = 7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7 FiscalMonthBaseIndex = 13 - StartofFiscalYear, adjustedFiscalMonthBaseIndex = if(FiscalMonthBaseIndex >= 12 or FiscalMonthBaseIndex < 0) then 0 else FiscalMonthBaseIndex, firstDayofWeek = Day.Monday, // set the week's start day, values: Day.Monday, Day, Sunday.... // configuration end FromDate = #date(FromYear,1,1), ToDate = #date(ToYear,12,31), Source = List.Dates( FromDate, Duration.Days(ToDate-FromDate) + 1, #duration(1,0,0,0) ), // Create the initial date column #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}), // Add the other date columns #"Inserted Date Id" = Table.AddColumn(#"Changed Type", "DateId", each Duration.Days([Date] - FromDate) + 1, Int64.Type), #"Inserted Year" = Table.AddColumn(#"Inserted Date Id", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Year Id" = Table.AddColumn(#"Inserted Year", "YearId", each Date.Year([Date]) - FromYear + 1, Int64.Type), #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year Id", "YearStart", each Date.StartOfYear([Date]), type date), #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "YearEnd", each Date.EndOfYear([Date]), type date), #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type), #"Inserted Month Id" = Table.AddColumn(#"Inserted Month", "MonthId", each Date.Month([Date]) + 12 * ([YearId] - 1), Int64.Type), #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month Id", "MonthStart", each Date.StartOfMonth([Date]), type date), #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "MonthEnd", each Date.EndOfMonth([Date]), type date), #"Inserted Month Name Long" = Table.AddColumn(#"Inserted End of Month", "MonthNameLong", each Date.ToText([Date],"MMMM"), type text), #"Inserted Month Name Short" = Table.AddColumn(#"Inserted Month Name Long", "MonthNameShort", each Date.ToText([Date],"MMM"), type text), #"Inserted Days in Month" = Table.AddColumn(#"Inserted Month Name Short", "MonthDays", each Date.DaysInMonth([Date]), Int64.Type), #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type), #"Inserted Day Name Long" = Table.AddColumn(#"Inserted Day", "DayNameLong", each Date.ToText([Date],"dddd"), type text), #"Inserted Day Name Short" = Table.AddColumn(#"Inserted Day Name Long", "DayNameShort", each Date.ToText([Date],"ddd"), type text), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name Short", "DayOfWeek", each Date.DayOfWeek([Date]), Int64.Type), #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "DayOfYear", each Date.DayOfYear([Date]), Int64.Type), #"Inserted Quarter" = Table.AddColumn(#"Inserted Day of Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type), #"Inserted Quarter Id" = Table.AddColumn(#"Inserted Quarter", "QuarterId", each Date.QuarterOfYear([Date]) + 4 * ([YearId] - 1), Int64.Type), #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter Id", "QuarterStart", each Date.StartOfQuarter([Date]), type date), #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "QuarterEnd", each Date.EndOfQuarter([Date]), type date), #"Inserted Quarter Name" = Table.AddColumn(#"Inserted End of Quarter", "QuarterName", each Number.ToText([Quarter], "Qtr 0"), type text), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter Name", "WeekOfYear", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type), #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "WeekOfMonth", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type), #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "WeekStart", each Date.StartOfWeek([Date],firstDayofWeek), type date), #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "WeekEnd", each Date.EndOfWeek([Date],firstDayofWeek), type date), #"Inserted Financial Base Date" = Table.AddColumn(#"Inserted End of Week", "FinancialBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex), type date), #"Inserted Financial Year" = Table.AddColumn(#"Inserted Financial Base Date", "FinYear", each Date.Year([FinancialBaseDate]), Int64.Type), #"Inserted Financial Quarter" = Table.AddColumn(#"Inserted Financial Year", "FinQuarter", each Date.QuarterOfYear([FinancialBaseDate]), Int64.Type), #"Inserted Financial Month" = Table.AddColumn(#"Inserted Financial Quarter", "FinMonth", each Date.Month([FinancialBaseDate]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Inserted Financial Month",{"FinancialBaseDate"}) in #"Removed Columns"