Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

M Query Editor - List Dates exactly one year from today

I have the following M query code to generate a list of dates:

 

= #"Future Date List"(#date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())), 365, #duration(1, 0, 0, 0))

 

I know this should generally work, but I need the list to make sure that if today is 12/1/19, that the last date in the list is 12/1/20. 

 

The code I currently have can possibly result in 11/30/20 being the last date in the list which would throw off future steps.

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try this script

     

    let
    today = Date.From(DateTime.LocalNow() ),
    futureDate = Date.AddYears(today,1),
    GetDateCount = Duration.Days(futureDate - today) + 1,
    GetDateList = List.Dates(today, GetDateCount, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})

in
    #"Changed Type"

     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous you should add step to substract today date from Dec 1, 2020, it will give you # of days and use that value in your future list instead of 365 days.

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Anonymous try this script

       

      let
    today = Date.From(DateTime.LocalNow() ),
    futureDate = Date.AddYears(today,1),
    GetDateCount = Duration.Days(futureDate - today) + 1,
    GetDateList = List.Dates(today, GetDateCount, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})

in
    #"Changed Type"

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2kThanks for the reply. Is there a way to do that in one step dynamically? As 12/1/20 is just an example and I don't want the date to be fixed. I want whatever the date is today and the ending date to be one year from that. So today, the list would be from 12/11/19-12/11/20. 

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Anonymous that should be very easy, so you are always adding 1 year to today's date, let me put together something for you quickly

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Is this problem solved?

     

    Best Regards,

    Icey