Forum Discussion
Adding a loop in Power Bi
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
3 Replies
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);}
hi, Jthathaisa
You could refer to this post about it.
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])
Best Regards,
Lin
