Calculation with previous month's value

kangx322 

there are few things you can try out.

A. If you are querying a database, run the loop on the server side.

B. Known PQ performance tuning

https://www.thebiccountant.com/speedperformance-aspects/

C. You can try List. Buffer as ronrsnfld mentioned

D. For calualtion if you prefre List.Generate, you can reduce one step like this

    Loop = List.Generate(
                        ()=>[i=0,k=Value{i}],
                        each [i]<List.Count(Value),
                        each [i=[i]+1, k=[k]*3+Value{i}*0.5],
                        each[k]
    )

E You can obtain same result by using an Accumulator too

  Accumulator = List.Skip(
    List.Accumulate(
      Value, 
      {0}, 
      (state, current) =>
        if current = Value{0} then
          state & {List.Last(state) + current}
        else
          state & {List.Last(state) * 3 + current * 0.5}
    )
  )

The combinded code is here and I am not sure wich one would give you better performance, you need to test it out.

The combined code is here.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMtQ1ACElHSUjC6VYHSQxI6CYOaqQMUiZGaqYCVDMxABVzBQoZogiZGgGEgJqjQUA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Date = _t, Value = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Value", Int64.Type}}), 
  Value = #"Changed Type"[Value], 
  Loop = List.Generate(
    () => [i = 0, k = Value{i}], 
    each [i] < List.Count(Value), 
    each [i = [i] + 1, k = [k] * 3 + Value{i} * 0.5], 
    each [k]
  ), 
  Accumulator = List.Skip(
    List.Accumulate(
      Value, 
      {0}, 
      (state, current) =>
        if current = Value{0} then
          state & {List.Last(state) + current}
        else
          state & {List.Last(state) * 3 + current * 0.5}
    )
  ), 
  Custom1 = Table.FromColumns(
    Table.ToColumns(#"Changed Type") & {Accumulator}, 
    List.Combine({Table.ColumnNames(#"Changed Type"), {"cumulativeValue"}})
  )
in
  Custom1

Would have been a lot easier performance wise, had it been achievable in DAX and I don't know if I can do recursion of this sort in DAXAlexisOlson