Planned Marketing spend split by month/quarter

[Anders_G]

Hi,

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.

Criterias: 

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. 
https://drive.google.com/file/d/12L9iKdDf76imDzz0XlkcO5KW9jl4eEsr/view?usp=sharing

[amitchandak]

@Anders_G , Refer if these two can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

or

https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[Anders_G]

Thank you, I will have a look and see if they will help.