Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

A measure/calculated column that calculates the budget exceeded decreases the plan in next months

Hey, I need help in the following. 


I have a budget for the whole year by months. If the value of orders exceeds the budget (GAP), the exceeded value decreases the budget in next months. Of course its not only decreases next month, but all, it depends on how big is gap.


If there is no gap, surplus doesn't increase budget :).


Below i put some example in excel how it should look like, and how data looks in PBI (its just one table). 





The example begins with April. Sorry, I;mconfused. I'm looking at your example and although the budget gap is in total -30000 you adjust budget by -50,000. I reposted the file with an update.




View solution in original post

Solution Sage
Solution Sage


I wanted to ask which month do you start adjusting the budget? In this example(I keyed in data very close to your Google Sheet) May would be reduced by over $2MM. Would you mind putting a few expected results for  some months? Are orders fixed? Will the only thing getting adjusted be budget?

*Data starts in April.




We have budget from 04.2022 to 03.2023. (fiskal year).

First adjust will be in May if orders exceed budget in April.

For example like in google sheets, first adjust is in July because there is a GAP in June (3,27mln) so July adjust is from 1,7mln to -1,57mln.

Only the budget will be adjusted.

Orders are not fixed, buyers sometimes change the delivery date and the value can jump from one month to another.


Good am:

I've tried a couple approaches, but wanted you to check it out. The date table is now adjusted to your fiscal year. After this, it should be reasonable to edit quickly, if needed. The second adj budget is only changing the budget when orders exceed budget. No changes for month one(April).


I hope this helps!


File attached. 




I dont get it :). Why in adjusted budget there is order value?

March - there is a gap 3,27mln, so in April Adjusted Budget should be 1,7mln - 3,27= -1,57mln

Now is just order value.



The example begins with April. Sorry, I;mconfused. I'm looking at your example and although the budget gap is in total -30000 you adjust budget by -50,000. I reposted the file with an update.




Hi, it works! Thank you for taking the time to resolve this 🙂

Solution Sage
Solution Sage

Hi Again:

I wanted to paste the results as I was interchanging "actuals" & "achieved". I changed them all to Actual.


Here is the simple data part.


If this is confusing in any way, please send eample data (excel is fine) and will show how to do this.


You can also choose to let actuals be actuals and leave the original FC in the future months or even put a parameter that adjusts the future months by the current FC trend. Lot's of options. Thanks..

Hi! Thanks for respond. It's little confusing and maybe not quite what I need. 

I put below some example in xlsx with simple formulas (GAP> 0  decrease budget in next month).


A short story of what this is all about and what users are supposed to get out of it: order value refers to the purchase value in which month the goods will arrive, if the value exceeds the budget, the surplus will reduce the budget in the following month and subsequent deliveries must be postponed to another subsequent months. 



Thank you. I understand more now. Can I do this in the next couple-few hours? Take care..

Solution Sage
Solution Sage


You can try a couple of measures. I am assuming you have monthly budget and are adjusting after month end. The first measure is the answer but I use a SUMX measure next to get the totals correctly.

Forecast full =
VAR forecaststartmonth =
MIN ( Table[Month] ),
FILTER ( ALL ( Table[Month] ), [Actuals] = 0 )
VAR budgettotal =
VAR achievedtotal =
VAR currentmix = [Mix:]
VAR newtable =
ADDCOLUMNS ( ALL ( Table[Month] ), "@Mix", [Mix:] ),
Table[Month] >= forecaststartmonth
VAR mixsum_after_achieved =
SUMX ( newtable, [@Mix] )
IF (
MAX ( Data[Month] ) >= forecaststartmonth,
( budgettotal - achievedtotal ) * currentmix / mixsum_after_achieved
Forecast Total fix: =
SUMX( VALUES( Data[Month] ), [Forecast full] )

All data field in one table called "Table"


I hope this helps.

Frequent Visitor

I think the best option here is calculated column and i tried something like this, but it only works for one row (NewBudget column), I want to make it cumulative, for example next month should be -233k, and next one -83k etc. because the gap rolls over to subsequent months reducing the budget. Maybe someone provide me how to resolve this. calculateColumn.PNG



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.