Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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
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
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
thank you very much for your help, this solution works well
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
Thanks for you help, both solutions work well !
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |