Dax for conducting a calculation each month on another measure

[Dataflows7]

I have a report which shows a static monthly forecast  and a measure that is linked to a parameter to allow the user to increase or decrease by a % the incoming and closure forecast per month. The measure should show the static forecast plus or minus the increase or decrease in incoming /closures.

Let's say we had 1000 static forecast for July and all months during the year , and 100 incoming and 50 closure monthly were set on the parameter the dynamic measure should show 1050 as the net is +50

this works fine for the current month but then next month the measure takes the 1000 + 50 whereas I want it to take the 1050 + the 50 so return 1100.

the problem I'm having is getting the 1050 to roll forward and be used in the next months calculation, easy to do in excel but not power bi 

tried using offset but it just doesn't return the new calculation of 1050

any ideas ?

[EricVieira]

O que você está tentando fazer — acumular previsões ajustadas mês a mês com base no valor do mês anterior — é um problema de cálculo recursivo ou iterativo, e de fato o DAX não é naturalmente recursivo. No entanto, é possível simular esse comportamento usando acúmulo progressivo controlado por ordenação temporal, com uso cuidadoso de SUMX, CALCULATE e tabelas virtuais.

Solução com padrão de Acúmulo Cumulativo
A ideia é criar uma medida que soma progressivamente os valores mês a mês, incluindo o ajuste, simulando o comportamento de "1050 em julho, depois 1100 em agosto, depois 1150 em setembro" e assim por diante.

Assumindo que você tenha:

[Static Forecast] — valor fixo (ex: 1000)

[Incoming] — valor de entrada (ex: +100)

[Closure] — valor de saída (ex: -50)
Um calendário com colunas Date[Month] e Date[MonthNumber]

Cumulative Adjusted Forecast := VAR CurrentMonth = MAX ( 'Date'[MonthNumber] ) RETURN SUMX ( FILTER ( ALL ( 'Date' ), 'Date'[MonthNumber] <= CurrentMonth ), [Incoming] - [Closure] ) + [Static Forecast]

Isso acumula (Incoming - Closure) mês a mês e soma com o valor inicial fixo.

Se o valor inicial (como 1000) for apenas no primeiro mês e não deve se repetir, substitua [Static Forecast] por algo como:

VAR InitialMonth = MINX(ALL('Date'), 'Date'[MonthNumber])
RETURN IF(CurrentMonth = InitialMonth, [Static Forecast], 0)

e integre à lógica acima.

Alternativa com Tabela Calculada (se realmente precisar de estado "anterior")
Se o comportamento recursivo puro for essencial (isto é, cada mês realmente depende do valor do mês anterior já ajustado), a forma mais precisa seria usar tabela calculada com coluna calculada acumulando valor mês a mês, algo como:

AdjustedForecast =
ADDCOLUMNS ( 'Date', "AdjustedValue", VAR CurrentMonth = 'Date'[MonthNumber] RETURN [Static Forecast] + SUMX ( FILTER ( 'Date', 'Date'[MonthNumber] <= CurrentMonth ), [Incoming] - [Closure] ) )

[v-agajavelly]

Hi @Dataflows7 ,

Just checking in once more were you able to implement the running total logic with the SUMX approach that super user suggested? Curious to hear how it performed across your monthly visuals. If you’ve had a chance to test it and everything’s working as expected, that’s great. If you ran into any edge cases or unexpected behavior, feel free to share details we’re happy to help fine-tune it further. Looking forward to your update.

Regards,
Akhil.

[v-agajavelly]

Hi @Dataflows7 ,

I’m following up to confirm that all items discussed have been addressed. If there’s anything else you need, I’d be happy to assist.

Thanks,
Akhil.

[Poojara_D12]

Hi @Dataflows7 

You're trying to build a rolling forecast measure in Power BI where each month's forecast is adjusted based on a static base and user-defined parameters for monthly incoming and closures. The challenge is that while your calculation works for the current month, it does not carry forward the adjusted value (e.g., July's 1050) into the next month (e.g., August), which should then become 1100, and so on. This kind of recursive or cumulative logic—where each month's value depends on the previous month's result—is straightforward in Excel because of its cell-based referencing but is more complex in Power BI due to its columnar and filter context-driven model.

Power BI doesn't natively support iterative row-by-row calculations across time without some workarounds. To achieve this, you need to simulate recursion using a running total pattern across months, leveraging DAX functions like DATESYTD, SUMX, or a combination of GENERATE and FILTER. One way to approach this is:

Create a calendar table with a month sequence column (e.g., MonthIndex) to establish order.

Use a SUMX over a filtered table that iterates from the first month to the current month, cumulatively adding (incoming - closure) to the base value.

In your measure, calculate:

Cumulative Forecast =
VAR CurrentMonthIndex = MAX('Date'[MonthIndex])
VAR Base = 1000
VAR MonthlyDelta = [IncomingParameter] - [ClosureParameter]
VAR MonthsPassed = 
    FILTER(
        ALL('Date'),
        'Date'[MonthIndex] <= CurrentMonthIndex
    )
RETURN
    Base + COUNTROWS(MonthsPassed) * MonthlyDelta

This pattern gives you a cumulative logic that mimics a rolling calculation month over month. Adjust the logic if incoming/closure parameters vary by month. If so, you’d need to SUMX over MonthsPassed and apply the correct monthly parameter values per row.

In short, the key to solving your issue is to simulate recursion via SUMX over a filtered time range, as DAX does not support Excel-style iterative dependencies directly.

[FBergamaschi]

I suggest a slightly different code, just for potential performance reasons

 CALCULATE ( 
     [Static Monthly Forecast] + ([Incoming Parameter Value] - [Closure Parameter Value]),
     'Date'[Date] <= MAX('Date'[Date])
 )

This code avoids context transition on each date, so I only wanted to optimize. Thanks to the other contributors for provifing the feedbacks!

If this helped, please give kudos to me and the other contributors of course (solution would not belong to me as the previous post already provided an optimum feedback in my opinion, this is just DAX refinement)

best

[v-agajavelly]

Hi @Dataflows7 ,

Thanks for your question really interesting scenario. And big thanks to @DataNinja777  for the thoughtful response

Your DAX solution using SUMX over a filtered date range is spot on if the goal is to show a compounded forecast over time, with each month building upon the prior months based on the net change (incoming - closure). This approach does exactly what Excel would do with a running total logic and should work well for a monthly time-series visual. I’d recommend implementing the solution exactly as shared and testing across months. Just make sure your 'Date' table is properly marked as a date table and connected correctly to your forecast data. Let us know how it goes.

Thanks,
Akhil.

[DataNinja777]

Hi @Dataflows7 ,

To create a DAX measure that calculates a running forecast compounding each month, you can use the following formula. This approach calculates the cumulative total of the static forecast plus the monthly net change from your parameters up to the current month in your report.

Dynamic Forecast =
SUMX(
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    ),
    [Static Monthly Forecast] + ([Incoming Parameter Value] - [Closure Parameter Value])
)

This DAX formula works by first identifying the current month in the visual using MAX('Date'[Date]). The FILTER function then creates a table of all dates from the very beginning of your 'Date' table up to and including this current month. Finally, SUMX iterates over this filtered table of dates. For each month in the iteration, it adds your [Static Monthly Forecast] to the net change calculated from your [Incoming Parameter Value] and [Closure Parameter Value]. By summing the results of this calculation for all months leading up to the current one, it creates the desired compounding effect, where each month's value builds upon the total of the previous months.

Best regards,