Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Planned Marketing spend split by month/quarter



Thanks in advance and I appreciate any help on this. 


I'm trying to calculate the marketing spend per month/quarter, as to be used in a budget tracker. A contract is signed on the signed date for a contracted amount and runs from the signed date to contract end date. Our objective is to spend the entire contract amount during the duration of the contract. The tracker shows the planned amounts that needs to be spent per month and quarter.



  1. Broken fiscal year. Fiscal Year starts on April 1st. Q1 Apr-Jun, Q2 Jul-Sept etc.
  2. Planned spending (budget) is not assigned to the quarter in which the contract was signed. If a contract was signed in Q1 the budget calculation starts in Q2. The budget should be split evenly between the remaining quarters (months) within the duration of the contract.
  3. Any amount spent is deducted from the quarter it was spent in. If the total spent amount in a quarter was lower than budgeted then the oustanding amount is split between the next quarters (same if the planned budget was overspent, the budget for the remaining months is lowered).

I have two data sets.

1. Opportunity, with information regarding the client and contract. Important numbers are Signed Date, Contract end date and Contract amount.

2. Spent Funds, shows between what dates the contracted amounts were spent. Start Date, End Date and Spent Amount.


The two tables are linked by the Opp ID. All dates are european format, YYYY-MM-DD.





The expected outcome is something similar to this. With the current quarter being Q2. (to be taken in to account for the signed date.)




Link to Pbix file.



Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors