Forecasting Overspend on contract

[MercedesAlpha1]

Hi

So im a novice to PowerBi but getting better

Basically what im trying to do is show on a contract that based on the current yearly average spend if i will breach the contract value this year or when the contract will hit its contract value.

My data table as the spend on the contract by invoice and the date of the invoice and I also have the contract value. How do i do this?

Thanks

[rohit1991]

Hi @MercedesAlpha1 ,
Based on your description, you're on the right track with using Power BI to forecast potential overspend on a contract. What you need to do is calculate the total spend so far, determine your average monthly spending, and then compare this against your total contract value to forecast when the contract might be breached. As suggested by Har**bleep**ha from the community team, you can use DAX to create key measures: one for total spend (SUM(Invoices[Spend])), one for the average monthly spend using AVERAGEX, and another to calculate how much of the contract value remains. Finally, by dividing the remaining value by the average monthly spend, you'll estimate how many months are left before you potentially exceed the contract value. Visualizing these insights in Power BI—using card visuals for totals and charts for trends—can help stakeholders quickly understand the financial trajectory of the contract. Let me know if you need help creating the actual DAX formulas or visuals.

[v-hjannapu · Accepted solution]

Hi @MercedesAlpha1,

Thank you for reaching out to the Microsoft fabric community forum.

You may calculate your total spend so far and average monthly spend using your invoice data in Power BI. Then, compare this against your contract value to see how much is left and estimate when you might exceed the contract if spending continues the same way. Using simple DAX measures and card visuals will help you track this easily.
Please find the attached file for your reference.

Contract Value = 120000

Total Spend = SUM(Invoices[Spend])

Invoice Month = FORMAT(Invoices[Invoice Date], "YYYY-MM")

Average Monthly Spend =

AVERAGEX(

  VALUES(Invoices[Invoice Month]),

  CALCULATE(SUM(Invoices[Spend]))

)

Remaining Value = [Contract Value] - [Total Spend]

Estimated Months to Breach =

DIVIDE([Remaining Value], [Average Monthly Spend])

This will estimate how many more months until you reach your contract limit.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team 

[v-hjannapu]

Hi @MercedesAlpha1 

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @MercedesAlpha1,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @MercedesAlpha1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank You.

Harshitha.

Community Support Team.