Dynamic monthly budget based on year budget minus spent budget

[hamidomar000m]

Hi,

I have some trouble getting this correct.
I have a budget of 100,00.00 for 2024. When I take the average monthly budget its 100000 / 12 = 8,333.33 each month in january. Lets say I spent 15000 in january which is over the monthly average budget, so the new budget in febrary should be (100000-15000) /  11 = 7727.27. If I spent 10000 in febrary, the budget in march should be (100000-15000-10000) /  10 = 7500. 
I want to create a measure so I can add this on "line and stacked column chart" where the columns represent the spend money in each month and the line would represent the dynamic budget for those month. 
Can you please help me with solving this issue?

[v-huijiey-msft]

Hi @hamidomar000m ,

Hope everthing is going well.

Please follow these steps:

1. This is the original data.

2. Use the following DAX syntax to extract the month of DateKey to create a calculated column.

Month = MONTH(SpendingTable[DateKey])

3. Define a metric to display the total annual budget.

Initial Budget = 100000

4. Calculate the actual amount spent each month.

SpendAll = SUMX(FILTER(ALL(SpendingTable),[Month]<MAX(SpendingTable[Month])),SpendingTable[SpendingAmount])

5. Calculate your total budget for each month - how much you actually spend.

RemainSpending = IF(MAX(SpendingTable[Month]) = 1,[Initial Budget],[Initial Budget] - [SpendAll])

6. A measure used in the denominator when calculating your monthly budget.

Num = 12 - MAX(SpendingTable[Month]) + 1

7. Create a measure that represents the dynamic budget for the month.

Dynamic Monthly Budget = DIVIDE(SpendingTable[RemainSpending],[Num])

8. Drag Month, SpendingAmount, and Dynamic Monthly Budget to the line chart for display.

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[hamidomar000m]

Dear Yand,

thank you very much for the solution! It does exactly what I needed but I got this error trying to work it for my data. 

Can you please let me know how I can fix it?

[v-huijiey-msft · Accepted solution]

Hi @hamidomar000m ,

Thank you very much for your reply and your patience.

Sorry, I defined 100,000 as the initial budget for the convenience of calculation.

If you want to define the initial budget as the sum of the dynamic budgets, please refer to my measure DAX syntax below:

Initial Budget = CALCULATE(SUM(SpendingTable[SpendingAmount]),ALL(SpendingTable))

Regarding your other question, it prompts you that [Month] does not exist. Please confirm whether your Month is a calculated column. In my second step, I defined Month as a calculated column instead of a measure.

Please check again and feel free to contact me if you have any further questions.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[hamidomar000m]

Hi @v-huijiey-msft 
I noticed somthing else in your solution. You defined 100000 as initial budget and the sum of dynamic budgets should add upto initial budget but when I added up all the dynamic budgets it ended up to be only 88200. so there is clearly an issue with the solution.
Can you please check what it could be?
Best regards, Hamid

[amitchandak]

@hamidomar000m , You can allocate the budget monthly and later subtract the actual amount, if need subtract YTD amount

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...