Date table two years from today instead of fixed date

[EZiamslow]

Hi,

My date table always starts from a fixed date 1/1/2017. How can I change it so it always starts two year from today's date?

let
    Source = #date(2017, 1, 1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "FY4", each "FY"& (if Date.Month([Date])<= 6 then Number.ToText( Date.Year([Date])) else Number.ToText(Date.Year([Date])+1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WW", each "WW" & (if Date.WeekOfYear([Date])-27+1<=0 then Number.ToText(52+Date.WeekOfYear([Date])-27+1) else Number.ToText( Date.WeekOfYear([Date])-27+1))),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Date", Order.Descending}}),
    #"Inserted First Characters" = Table.AddColumn(#"Sorted Rows", "First Characters", each Text.Start([FY4], 2), type text),
    #"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End([FY4], 2), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FY"),
    #"Added Conditional Column" = Table.AddColumn(#"Merged Columns", "Custom", each if [WW] = "WW1" then "WW01" else if [WW] = "WW2" then "WW02" else if [WW] = "WW3" then "WW03" else if [WW] = "WW4" then "WW04" else if [WW] = "WW5" then "WW05" else if [WW] = "WW6" then "WW06" else if [WW] = "WW7" then "WW07" else if [WW] = "WW8" then "WW08" else if [WW] = "WW9" then "WW09" else [WW]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"FY4", "WW"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"FY", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"FYWW_C_PQ")
in
    #"Merged Columns1"

[Greg_Deckler]

I believe it is DateTime.LocalNow()

[Mariusz · Accepted solution]

Hi @EZiamslow 

You can adjust your query accordingly

let
    today = Date.From( DateTime.LocalNow() ),
    start = Date.AddYears( today, -2 ),
    Custom1 = List.Dates(start, Number.From( today )- Number.From( start ), #duration(1,0,0,0)),
    // top 3 steps adjusted

    #"Sorted Items" = List.Sort(Custom1,Order.Ascending),
    #"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "FY4", each "FY"& (if Date.Month([Date])<= 6 then Number.ToText( Date.Year([Date])) else Number.ToText(Date.Year([Date])+1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WW", each "WW" & (if Date.WeekOfYear([Date])-27+1<=0 then Number.ToText(52+Date.WeekOfYear([Date])-27+1) else Number.ToText( Date.WeekOfYear([Date])-27+1))),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Date", Order.Descending}}),
    #"Inserted First Characters" = Table.AddColumn(#"Sorted Rows", "First Characters", each Text.Start([FY4], 2), type text),
    #"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End([FY4], 2), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FY"),
    #"Added Conditional Column" = Table.AddColumn(#"Merged Columns", "Custom", each if [WW] = "WW1" then "WW01" else if [WW] = "WW2" then "WW02" else if [WW] = "WW3" then "WW03" else if [WW] = "WW4" then "WW04" else if [WW] = "WW5" then "WW05" else if [WW] = "WW6" then "WW06" else if [WW] = "WW7" then "WW07" else if [WW] = "WW8" then "WW08" else if [WW] = "WW9" then "WW09" else [WW]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"FY4", "WW"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"FY", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"FYWW_C_PQ")
in
    #"Merged Columns1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[EZiamslow]

Thank you. I learned something today.