DateDim.M

let
    // The List.Dates function that returns a list of dates
    Source = List.Dates,
    // Invoke List.Dates function. Here you need to specify the dates you want the list to invoke.
    // From Date: # Date (YYYY,MM,DD)
    // To adapt this to your own calender table change the values for (YYYY; MM; DD)
    // Count: It counts the number of days between the start of the first (Calendar) year (set by Year parameter) and the end of the current Reporting Year (set by Month parameter).
    // #duration (1,0,0,0) instructs List.Date to do one day increments in the list.
    #"Invoke dates" = Source(#date(#"Year" , 1 , 1 ), Duration.Days ( Date.AddMonths ( #date ( Date.Year ( DateTime.FixedLocalNow() ) + 1 , 1, 1 ) , #"Month" - 1 ) - #date ( #"Year" , 1 , 1 ) ) , #duration(1, 0, 0, 0)),
    // This step will convert the list to a table.
    // To set the correct dates, edit the query parameters
    #"List to table" = Table.FromList(#"Invoke dates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Rename column to Date
    Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
    #"Added Reporting Date" = Table.AddColumn(Date, "Reporting Date", each Date.AddMonths ( [Date] , 13 - #"Month" )),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Reporting Date",{{"Date", type date}, {"Reporting Date", type date}}),
    // Extract Day from Date column and add leading zeros “00”
    #"Added Day" = Table.AddColumn(#"Changed Type", "Day", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
    // Extract Day Name from Date column using american names.
    // Navigate here for other Languages not included in the parameters: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx
    #"Added Day Name" = Table.AddColumn(#"Added Day", "Day Name", each Date.ToText([Date],"ddd",#"Language")),
    //Extract Month No from Date column    
    #"Added Month No" = Table.AddColumn(#"Added Day Name", "Month No", each Date.Month([Date])),
    #"Added Reporting Month" = Table.AddColumn(#"Added Month No", "Reporting Month No", each if [Month No] - #"Month" + 1 < 1 then [Month No] - #"Month" + 13 else [Month No] - #"Month" + 1 ),
    // Extract Month Name from Date column.
    // Navigate here for other Languages: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx
    #"Added Month Name" = Table.AddColumn(#"Added Reporting Month", "Month Name", each Date.ToText([Date],"MMM",#"Language")),
    //Extract Quarter No from Date column
    #"Added Quarter" = Table.AddColumn(#"Added Month Name", "Quarter", each Date.QuarterOfYear([Date])),
    #"Added Reporting Quarter" = Table.AddColumn(#"Added Quarter", "Reporting Quarter", each Date.QuarterOfYear( [Reporting Date] )),
    //Extract Week No from Date column
    #"Added Week No" = Table.AddColumn(#"Added Reporting Quarter", "Week No", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])-1),2,"0")),
    // Extract Year from Date column
    #"Added Year" = Table.AddColumn(#"Added Week No", "Year", each Date.Year([Date])),
    #"Added Reporting Year" = Table.AddColumn(#"Added Year", "Reporting Year", each "RY" & Text.From ( Date.Year ( [Reporting Date] ) ) ),
    // Concatenate Year and Month Name
    #"Added Year-Month" = Table.AddColumn(#"Added Reporting Year", "Year-Month", each Number.ToText ( [Year] ) & " - " & [Month Name] ),
    #"Added Reporting Year-Month" = Table.AddColumn(#"Added Year-Month", "Reporting Year-Month", each [Reporting Year] & " - " & [Month Name]),
    // Concatenate Year and Quarter No with leading zeros on quarter Nos
    #"Added Year Quarter" = Table.AddColumn(#"Added Reporting Year-Month", "Year-Quarter", each Number.ToText([Year]) & " Q"& Number.ToText([Quarter],"0")),
    #"Added Reporting Year-Quarter" = Table.AddColumn(#"Added Year Quarter", "Reporting Year-Quarter", each [Reporting Year] & " Q" & Number.ToText ( [Reporting Quarter] , "0" )),
    #"Added Current Date.1" = Table.AddColumn(#"Added Reporting Year-Quarter", "Current Date.1", each DateTime.LocalNow ()),
    //Change field types to TEXT
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Current Date.1",{ {"Day", type text}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter", type text}, {"Year-Quarter", type text}, {"Year-Month", type text}, {"Current Date.1", type date}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Current Date.1", Date.StartOfMonth, type date}}),
    #"Added Current Date" = Table.AddColumn(#"Calculated Start of Month", "Current Date", each Date.AddDays ( [Current Date.1] , -1 )),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Current Date",{{"Current Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Current Date.1"}),
    #"Added Current Reporting Date" = Table.AddColumn(#"Removed Columns", "Current Reporting Date", each Date.AddMonths ( [Current Date] , 13 - #"Month" )),
    #"Added Current Day" = Table.AddColumn(#"Added Current Reporting Date", "Current Day", each if [Date] = [Current Date] then "Current Day" else if [Date] = Date.AddDays ( [Current Date] , -1 ) then "Prior Day" else if [Date] >= Date.AddDays ( [Current Date] , -6 ) and [Date] < [Current Date] then "Prior Day -" & Text.From ( Duration.Days ( [Current Date] - [Date] ) - 1 )  else "Other" ),
    #"Added Current Month Sort - Base" = Table.AddColumn(#"Added Current Day", "Current Month Sort - Base", each if Date.StartOfMonth ( [Date] ) < Date.StartOfMonth ( [Current Date] ) then 1 - List.Count ( DateDatesBetween ( Date.StartOfMonth ( [Date] ) , Date.StartOfMonth ( [Current Date] ) , "Month" ) ) else List.Count ( DateDatesBetween ( Date.StartOfMonth ( [Current Date] ) , Date.StartOfMonth ( [Date] ) , "Month" ) ) - 1 ),
    #"Added Current Month Sort" = Table.AddColumn(#"Added Current Month Sort - Base", "Current Month Sort", each if [#"Current Month Sort - Base"] >= -13 and [#"Current Month Sort - Base"] <= 13 then [#"Current Month Sort - Base"] else -999),
    #"Added Current Month" = Table.AddColumn(#"Added Current Month Sort", "Current Month", each if [Current Month Sort] = -999 then "Other" else if [Current Month Sort] = 0 then "Current Month" else if [Current Month Sort] > 0 then "Future Month +" & Text.From ( [Current Month Sort] ) else if [Current Month Sort] < 0 then "Prior Month " & Text.From ( [Current Month Sort] ) else "Other"),
    #"Added Current Year" = Table.AddColumn(#"Added Current Month", "Current Year", each if [Year] = Date.Year ( [Current Date] ) then "Current Year" else if [Year] = Date.Year ( [Current Date] ) - 1 then "Prior Year" else if [Year] = Date.Year ( [Current Date] ) + 1 then "Next Year" else if [Year] >= Date.Year ( [Current Date] ) -10 and [Year] < Date.Year ( [Current Date] ) then "Prior Year -" & Text.From ( Date.Year ( [Current Date] ) - [Year] - 1 )  else "Other"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Current Year",{"Current Month Sort - Base"}),
    #"Added MTD" = Table.AddColumn(#"Removed Columns1", "MTD", each if [Year] = Date.Year ( [Current Date] ) and [Month No] = Date.Month ( [Current Date] ) and [Date] <= [Current Date] then "Current MTD" else if [Year] = Date.Year ( Date.AddMonths ( [Current Date] , -1 ) ) and [Month No] = Date.Month ( Date.AddMonths ( [Current Date] , -1 ) ) and [Date] <= Date.AddMonths ( [Current Date] , -1 ) then "Prior MTD" else "Other" ),
    #"Added YTD" = Table.AddColumn(#"Added MTD", "YTD", each if [Year] = Date.Year ( [Current Date] )  and [Date] <= [Current Date] then "Current YTD" else if [Year] = Date.Year ( [Current Date] ) - 1 and [Date] <= Date.AddYears ( [Current Date] , -1 ) then "Prior YTD" else "Other" ),
    #"Added Reporting YTD" = Table.AddColumn(#"Added YTD", "Reporting YTD", each if Date.Year ( [Reporting Date] ) = Date.Year ( [Current Reporting Date] )  and [Reporting Date] <= [Current Reporting Date] then "Current Reporting YTD" else if Date.Year ( [Reporting Date] ) = Date.Year ( [Current Reporting Date] ) - 1 and [Reporting Date] <= Date.AddYears ( [Current Reporting Date] , -1 ) then "Prior Reporting YTD" else "Other"),
    // Extract DayName from Date column to sort the previously created Day Name column and use parameters to set if the Day starts on Monday or Sunday.
    #"Added Day Name Sort" = Table.AddColumn(#"Added Reporting YTD", "Day Name Sort", each Date.DayOfWeek([Date],#"StartWeekDay")),
    // Concatenate Year and Month Number to create a column to sort the previously created MonthYear column
    #"Added Year-Month Sort" = Table.AddColumn(#"Added Day Name Sort", "Year-Month Sort", each Text.From ( [Year] ) & Text.PadStart ( Text.From ( [Month No] ) , 2 , "0") ),
    #"Added Reporting Year-Month Sort" = Table.AddColumn(#"Added Year-Month Sort", "Reporting Year-Month Sort", each [Reporting Year] & Text.PadStart ( Text.From ( [Reporting Month No] ) , 2 , "0")),
    #"Added Current Day Sort" = Table.AddColumn(#"Added Reporting Year-Month Sort", "Current Day Sort", each if [Current Day] = "Other" then 999 else Duration.Days ( [Current Date] - [Date] ) ),
    #"Added Current Year Sort" = Table.AddColumn(#"Added Current Day Sort", "Current Year Sort", each if [Current Year] = "Other" then 999 else Date.Year ( [Current Date] ) - [Year]),
    #"Added YTD Sort" = Table.AddColumn(#"Added Current Year Sort", "YTD Sort", each if [YTD] = "Other" then 999 else Date.Year ( [Current Date] ) - [Year] ),
    #"Added Reporting YTD Sort" = Table.AddColumn(#"Added YTD Sort", "Reporting YTD Sort", each if [Reporting YTD] = "Other" then 999 else Date.Year ( [Current Reporting Date] ) - Date.Year ( [Reporting Date] )),
    #"Added MTD Sort" = Table.AddColumn(#"Added Reporting YTD Sort", "MTD Sort", each if [MTD] = "Other" then 999 else ( ( Date.Year ( [Current Date] ) * 12 ) + Date.Month ( [Current Date] ) ) - ( ( Date.Year ( [Date] ) * 12 ) + Date.Month ( [Date] ) ) ),
    //Change the field type for the sort columns to numbers
    #"Changed Type3" = Table.TransformColumnTypes(#"Added MTD Sort",{{"Year-Month Sort", Int64.Type}, {"Day Name Sort", Int64.Type}, {"Reporting Month No", Int64.Type}, {"Reporting Year-Month Sort", type text}, {"Current Day Sort", Int64.Type}, {"Current Year Sort", Int64.Type}, {"YTD Sort", Int64.Type}, {"MTD Sort", Int64.Type}, {"Current Month Sort", Int64.Type}})
in
    #"Changed Type3"