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

[krisstok]

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). 

[Whitewater100]

Hi:

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.

Thanks..

[krisstok]

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.

[Whitewater100]

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.

https://drive.google.com/file/d/1LO6O_ovz7gMTcf21aQoa5jhlY4S7aVQD/view?usp=sharing 

[krisstok]

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.

[Whitewater100 · Accepted solution]

Hi:

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.

[krisstok]

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

[Whitewater100]

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..

[krisstok]

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.

https://docs.google.com/spreadsheets/d/1OH5NX3txQ08TnDkRJTuRKUYdjMOheSAG/edit?usp=sharing&ouid=11005... 

[Whitewater100]

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

[Whitewater100]

Hi:

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 =

CALCULATE (

MIN ( Table[Month] ),

FILTER ( ALL ( Table[Month] ), [Actuals] = 0 )

)

VAR budgettotal =

CALCULATE ( [Budget:], REMOVEFILTERS () )

VAR achievedtotal =

CALCULATE ( [Actuals], REMOVEFILTERS () )

VAR currentmix = [Mix:]

VAR newtable =

FILTER (

ADDCOLUMNS ( ALL ( Table[Month] ), "@Mix", [Mix:] ),

Table[Month] >= forecaststartmonth

)

VAR mixsum_after_achieved =

SUMX ( newtable, [@Mix] )

RETURN

IF (

MAX ( Data[Month] ) >= forecaststartmonth,

( budgettotal - achievedtotal ) * currentmix / mixsum_after_achieved

,[Actuals])

Forecast Total fix: =

SUMX( VALUES( Data[Month] ), [Forecast full] )

All data field in one table called "Table"

I hope this helps.

[krisstok]

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.