Forum Discussion

alex753's avatar
alex753
Frequent Visitor
3 years ago
Solved

iterative/recursive calculations in Power Query or DAX

Hi,
I'm a beginner and I'd like to know if it's possible to make certain iterative calculations. In my case I would like to be able to obtain a column that allows me to obtain a performance over my period using daily performance. Currently I'm stuck I can not make the accumulation taking the value -1 of my column for my line N Cells D3 ,D4 .... In my column D is the result i wish to obtain.  Column F is the formula. I put you a screenshot so that my problem is clearer for you.
Thanks again to those who will help me.

 

 

 

 

 

Need Help
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, alex753 either List.Generate

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dv = Source[daily variation],
    dv_count = List.Count(dv),
    gen = 
        List.Generate(
            () => [i = 0, result = 100 + dv{0}],
            (x) => x[i] < dv_count,
            (x) => [i = x[i] + 1, result = x[result] * (1 + dv{i})],
            (x) => x[result]
        ),
    result = Table.FromColumns(Table.ToColumns(Source) & {gen}, Table.ColumnNames(Source) & {"Result I want"})
in
    result

     

    or List.Accumulate

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dv = Source[daily variation],
    dvp = List.Skip(List.Positions(dv)),
    acc = 
        List.Accumulate(
            dvp,
            {100 + dv{0}},
            (s, c) => s & {List.Last(s) * (1 + dv{c})}
        ),
    result = Table.FromColumns(Table.ToColumns(Source) & {acc}, Table.ColumnNames(Source) & {"Result I want"})
in
    result

     

     

  • slorin's avatar
    slorin
    3 years ago

    Hi

    Another solution

    let
    Source = YourSource ,
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    Daily_variation = List.Transform({0} & List.Skip(Index[daily variation]), each _ +1),
    First_variation = List.First(Index[daily variation]),
    Result = Table.AddColumn(Index, "Result", each (100+First_variation) * List.Product(List.FirstN(Daily_variation,[Index])))
    in
    Result

     

    Stéphane 

4 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, alex753 either List.Generate

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dv = Source[daily variation],
    dv_count = List.Count(dv),
    gen = 
        List.Generate(
            () => [i = 0, result = 100 + dv{0}],
            (x) => x[i] < dv_count,
            (x) => [i = x[i] + 1, result = x[result] * (1 + dv{i})],
            (x) => x[result]
        ),
    result = Table.FromColumns(Table.ToColumns(Source) & {gen}, Table.ColumnNames(Source) & {"Result I want"})
in
    result

     

    or List.Accumulate

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dv = Source[daily variation],
    dvp = List.Skip(List.Positions(dv)),
    acc = 
        List.Accumulate(
            dvp,
            {100 + dv{0}},
            (s, c) => s & {List.Last(s) * (1 + dv{c})}
        ),
    result = Table.FromColumns(Table.ToColumns(Source) & {acc}, Table.ColumnNames(Source) & {"Result I want"})
in
    result

     

     

    • alex753's avatar
      alex753
      Frequent Visitor
      3 years ago

      Thanks for you help, both  solutions work well !

       

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    Another solution

    let
    Source = YourSource ,
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    Daily_variation = List.Transform({0} & List.Skip(Index[daily variation]), each _ +1),
    First_variation = List.First(Index[daily variation]),
    Result = Table.AddColumn(Index, "Result", each (100+First_variation) * List.Product(List.FirstN(Daily_variation,[Index])))
    in
    Result

     

    Stéphane 

    • alex753's avatar
      alex753
      Frequent Visitor
      3 years ago

      thank you very much for your help, this solution works well