Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anders_G
Helper I
Helper I

Planned Marketing spend split by month/quarter

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.

 

Anders_G_2-1658825914333.png

Anders_G_4-1658825941463.png

 

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

 

Anders_G_5-1658830984154.png

 

Link to Pbix file. 
https://drive.google.com/file/d/12L9iKdDf76imDzz0XlkcO5KW9jl4eEsr/view?usp=sharing

 

2 REPLIES 2

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.