cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krisstok
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). 

sample_xls.PNG

pbi.PNG

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

 

Whitewater100_0-1653005463743.png

 

View solution in original post

11 REPLIES 11
Whitewater100
Super User
Super User

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

Whitewater100_0-1652923855302.png

 

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.

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

 

Whitewater100_0-1652970935327.png

 

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.

 

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.

 

Whitewater100_0-1653005463743.png

 

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

Whitewater100
Super User
Super User

Hi Again:

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

Whitewater100_0-1652903281200.png

Here is the simple data part.

Whitewater100_1-1652903365687.png

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.

 

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

 

 

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

Whitewater100
Super User
Super User

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] )
 
Whitewater100_0-1652900545728.png

All data field in one table called "Table"

 

I hope this helps.

krisstok
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

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors