M Query Editor - List Dates exactly one year from today

[Anonymous]

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.

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[parry2k]

@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 · Accepted solution]

@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]

@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]

@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