Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning All, I hope you are all safe in current situation!
What I am trying to do is the loop which should check when the sum of the rows in the Plus column is higher than the value in the Difference column, when it finds this sum, it should show it in the first row where the difference is less than zero.
Table which I do have right now:
YearWeek | Difference | Plus | Minus |
202013 | -5160 | 0 | -5160 |
202014 | -5239 | 0 | -79 |
202015 | -8415 | 79 | -3255 |
202016 | -6827 | 1588 | 0 |
202017 | 1586 | 8413 | 0 |
202018 | 6348 | 4762 | 0 |
202024 | 6269 | 0 | -79 |
202025 | 10769 | 4500 | 0 |
202028 | 10690 | 0 | -79 |
202029 | 8579 | 4800 | -6911 |
202030 | 7785 | 0 | -794 |
202033 | 78 | 0 | -7707 |
202045 | -1 | 0 | -79 |
202052 | 4499 | 4500 | 0 |
What would be perfect if possible:
Row | YearWeek | Difference | Plus | Minus | Suggestion | New Difference |
1 | 202013 | -5160 | 0 | -5160 | 10080 | 4920 |
2 | 202014 | -5239 | 0 | -79 | 0 | 4841 |
3 | 202015 | -8415 | 79 | -3255 | 0 | 1586 |
4 | 202016 | -6827 | 1588 | 0 | 0 | 1586 |
5 | 202017 | 1586 | 8413 | 0 | 0 | 1586 |
6 | 202018 | 6348 | 4762 | 0 | 4762 | 6348 |
7 | 202024 | 6269 | 0 | -79 | 0 | 6269 |
8 | 202025 | 10769 | 4500 | 0 | 4500 | 10769 |
9 | 202028 | 10690 | 0 | -79 | 0 | 10690 |
10 | 202029 | 8579 | 4800 | -6911 | 4800 | 8579 |
11 | 202030 | 7785 | 0 | -794 | 0 | 7785 |
12 | 202033 | 78 | 0 | -7707 | 0 | 78 |
13 | 202045 | -1 | 0 | -79 | 4500 | 4499 |
14 | 202052 | 4499 | 4500 | 0 | 0 | 4499 |
Suggestion Row1 = if Difference Row1 (-5160)<0,
Difference Row1 *(-1) < Plus Row1 -> No
Difference Row1 *(-1) < Plus Row1 + Plus Row2 -> No
Difference Row1 *(-1) < Plus Row1 + Plus Row2 + Plus Row3-> No
Difference Row1 *(-1) < Plus Row1 + Plus Row2 + Plus Row3 + Plus Row4-> No
Difference Row1 *(-1) < Plus Row1 + Plus Row2 + Plus Row3 + Plus Row4 + Plus Row5-> Yes
(0 + 0 + 79 + 1588 + 8413) = 10080 > 5160
If yes then move calculated (10080) to the first row where Difference is less than 0.
*Values added earlier cannot be used for the next lines
New Difference is showing how we will go if we will use Suggestion column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |