Forum Discussion

ben-t's avatar
ben-t
Frequent Visitor
3 years ago
Solved

Help - List.Generate dates with variable frequency in months

Hi, I'm trying to generate a list of jobs to do in the next 2 years I have a working custom column that provides me the list of dates.  But my jobs have varying frequencies of 2-12 months intervals...
Need Help
  • HotChilli's avatar
    HotChilli
    3 years ago

    It might be a field access error.

    Try

    let freq = [FREQUENCY] in each Date.AddMonths( _ , freq))

     

ben-t's avatar
ben-t
Frequent Visitor
3 years ago

It generates without issue once i added in this line
Thanks! 

GekkeHellie's avatar
GekkeHellie
Regular Visitor
3 years ago

Hi ben-t,

 

Could you share the full code that worked for you?

I think I've tried every possible code with the above solution, but I still get an error.

 

Thanks!

  • ben-t's avatar
    ben-t
    Frequent Visitor
    3 years ago

    Hi GekkeHellie

    this is the code i used

    let freq = [FREQUENCY] in
    List.Generate(
    () => [Next job],
    each Date.From(_) <= Date.From(Date.AddYears(Date.EndOfYear(DateTime.LocalNow()),1)),
    each Date.AddMonths(_, freq))

     

     

    • GekkeHellie's avatar
      GekkeHellie
      Regular Visitor
      3 years ago

      Hi ben-t ,

       

      Your answer definitely helped me into the right direction.

      In the table there is a start date, end date and frequency and I also wanted to create a new row between the two dates based on de frequency.

       

      My table looks like this:

       

      But since I have a end date in a column, instead of a formula like yours, I still got a error.

      So I also added the below line to the code:

      let enddate = [End Date]

      let freq = [Frequency] in
      let enddate = [End Date] in
      List.Generate(
      () => [Start Date],
      each Date.From(_) <= Date.From(enddate),
      each Date.AddDays(_, freq))

       

      Thanks for helping me out!