I am transferring an Excel report to Power BI, and I have a data field called ‘Position’ that is evaluated every month. For the current month, the position remains unchanged. However, for the next month, it will be calculated as follows: if the current month’s position is negative, it will be the sum of the next month’s position and the current month’s position. If this sum is still negative, in the third subsequent month, the position will be the result of the third month plus the sum of the second month. Each Excel cell follows this formula:

Position(i)= if( Position(i−1)<0 then Position(i)+Position(i-1) else Position(i))

, where Position(i−1) is calculated as :

Position(i−1)= if(Position(i−2)<0, Position(i−1)+Position(i−2), Position(i−1))

I’ve tried various ways to perform this calculation, including using the built-in visual calculation option, but none of them had the desired effect. To be as clear as possible, I’ll explain the simplest method I devised to arrive at the result.

bs_Position = 
VAR YearMonthCurrent = MAX('dcalendariosimulacao'[AnoMes])
VAR YearMonthPrevious = CALCULATE(MAX('dcalendariosimulacao'[AnoMes]), FILTER(ALL('dcalendariosimulacao'), 'dcalendariosimulacao'[AnoMes] < AnoMesAtual))
VAR ResulPrevious =
    CALCULATE(
        SUM('simulacao rolagem'[ConsumoPorProduto]) + [bs_capacidade],
        'dcalendariosimulacao'[AnoMes] = YearMonthPrevious
    )
VAR Result = 
    CALCULATE(
        SUM('simulacao rolagem'[ConsumoPorProduto]) + [bs_capacidade] + IF(ResulPrevious < 0, ResulPrevious, 0),
        'dcalendariosimulacao'[AnoMes] = YearMonthCurrent
    )

RETURN

Result

In this approach, I check if the previous result is negative, and if so, I add it to the current position. However, I can only obtain the result for the current position. So, if we have
position(i)=Position(i)+Position(i−1),
with the method I used, for position(i+1)(considering position(i)<0), the only value I get from the penultimate position is position(i). But what I actually need is Position(i)+Position(i−1).Below is the image of the data I obtained. But the correct result should be:
| CIDADE | 2024-06 | 2024-07 | 2024-08 | 2024-09 | 2024-10 | 2024-11 | 2024-12 |
|————–|———|———|———|———|———|———|———|
|RONDONÓPOLIS | | | | | | | |
| Position |-2.141 |932 | 0 |-7.902 |-1.246 |-28.294 |-4.288 |
| Cumulative Position|-2.140,63|-1.208,30|-1.208,30|-9.110,3|-10.356,62|-38.650,62|-42.938,62|
|RIO VERDE | | | | | | | |
| Position |-1.948 |-962 |-0 |-666 |-0 |-0 | 0 |
| Cumulative Position|-1.948|-2.910|-2.910 |-3576 |-3576 |-3576 | -3576 |
|Rio de Janeiro| | | | | | | |
| Position |-1.948 |2000 |-0 |1200 |-700 |-300 |3500 |
| Cumulative Position|-1.948|52 |0 |1200 |-700 |-1000 | 2500 |