cancel
Showing results 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).

1 ACCEPTED SOLUTION
Solution Sage

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.

11 REPLIES 11
Solution Sage

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

Frequent Visitor

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.

Solution Sage

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.

Frequent Visitor

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.

Solution Sage

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.

Frequent Visitor

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

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

Frequent Visitor

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.

Solution Sage

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

Solution Sage

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.

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.