Adding a loop in Power Bi

[Jthathaisa]

Hi Team,

How does one add this loop below in power BI 

let start = 100;
const rows = [];

for (let i = 0; i < rows.length; i++) {
start = start * (rows[i].priceChange + 1);
}

The aim of this loop is to calculate, my price change using this formula

[Initial value, starting at 100]100 * (price change +1 )

but while my price change is zero, the formula changes to

[The value derived from the first formula] previous value * (price change +1)

Thank you

[v-lili6-msft]

hi, @Jthathaisa 

You could refer to this post about it.

For and While Loops in DAX

https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

For your case, just do some adjustment as below:

For Loop = 
// Provide some starting values
VAR __n = 5
VAR __sum = 100
// Generate a "loop table", this will emulate a for loop for i=1 to some number
VAR __loopTable = GENERATESERIES(1,__n)
// Add in our calculated sum, emulating calculations done as iterations over the loop
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + PRODUCTX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
// Determine our MAX interation, the maximum value for "i"
VAR __max = MAXX(__loopTable1,[Value])
RETURN
// Return the value associated with the maximum value of "i" which is the last iteration in our "loop"
MAXX(FILTER(__loopTable1,[Value]=__max),[__sum])

By the way, you'd better learn PRODUCTX Function and GENERATESERIES Function.

Best Regards,

Lin

[Anonymous]

Could you please post sample data and desired outcome?

[Jthathaisa]

Sample Data:

Price change	Formula	Desired outcome
0.00	100 * (Price Change +1)	100
-0.02	100 * (Price Change +1)	98
-0.05	100 * (Price Change +1)	95
0.00	95* (Price Change +1)	95
0.01	95* (Price Change +1)	95.95
0.00	95.95* (Price Change +1)	95.95
-0.02	95.95* (Price Change +1)	94.031

so my starting formula is 100 *(price change +1), and continues until my price change = 0.00, if my price change = 0.00 then take the pervious desired outcome  and substitue the 100 with the desired outcome i.e  95* (Price Change +1)

I believe my loop is as follows :

let start = 100;

let price=0;

const rows = [];

for (let i = 0; i < rows.length; i++) {

start = rows[i].priceChange === 0.00 ? desiredoutcome : start;

price = start * (rows[i].priceChange + 1);

}