Forum Discussion

rahul_ferns's avatar
rahul_ferns
Helper II
5 years ago
Solved

Dynamic Calendar with Fixed Start Date and Dynamic End Date

Hi Experts

I have found a calendar in this formun that I like, but want to make a change to the start date. I would like the start date fixed to 2019,1,1 and the end date dynamic like in the code (current year plus 12 months)

Can someone please help me change the below to code?

 

let
Source = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
StartDate = DateTime.Date(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),-1))),
EndDate = DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(),12))),
DurationDays = Duration.Days(EndDate - StartDate),
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
#"Changed Type"

 

Thanks

  • Here you go.

    let
    Source = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    StartDate = #date(2019,1,1),
    EndDate = Date.From(Date.EndOfYear(Date.AddMonths(DateTime.LocalNow(),12))),
    DurationDays = Duration.Days(EndDate - StartDate),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
    in
    #"Changed Type"

     

    Pat

     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    Here you go.

    let
    Source = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    StartDate = #date(2019,1,1),
    EndDate = Date.From(Date.EndOfYear(Date.AddMonths(DateTime.LocalNow(),12))),
    DurationDays = Duration.Days(EndDate - StartDate),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
    in
    #"Changed Type"

     

    Pat

     

    • rahul_ferns's avatar
      rahul_ferns
      Helper II

      Thanks, Pat

      Worked just as how I wanted

       

      Much appreciated