Forum Discussion

katzaway's avatar
katzaway
Copper Contributor
Dec 15, 2023

partial month revenue allocation

I have a spreadsheet of customer data.  I have a column representing total contract amount and divide the spend by number of months to give me monthly allocation.  I am trying to do analysis where I break that spend out in each individual month to show me the run rate each month.    That part is easy when the contract starts the 1st of each month.  However, when the contract starts midmonth I want to only show half the amount in the first and last month.  In addition, with multi year deals I need to show the last month to calculate 50% of year 1 and 50% of year 2.

 

I am using sumifs.  Formula is =SUMIFS(Data[Monthly Allocation], Data[Client Name], $A5, Data[Start Date], "<="&E$4, Data[End Date],">="&E$4)

 

Whereas E4=current month; A5 = start date

 

Need help.

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    Hi katzaway 
    You can use a formula that checks if the current month (E4) is the same as the start or end month of the contract. If it is, then only allocate half of the monthly allocation. Otherwise, allocate the full amount.
    Here’s an example approach formula:

    =IF(OR(EOMONTH(E$4,0)=EOMONTH(Data[Start Date],0), 
    EOMONTH(E$4,0)=EOMONTH(Data[End Date],0)), 
    SUMIFS(Data[Monthly Allocation], Data[Client Name], $A5, Data[Start Date], "<="&E$4, Data[End Date],">="&E$4)/2, 
    SUMIFS(Data[Monthly Allocation], Data[Client Name], $A5, Data[Start Date], "<="&E$4, Data[End Date],">="&E$4))

    This formula checks if the current month (E4) is the same as the start or end month of the contract. If it is, it divides the monthly allocation by 2. If not, it uses the full monthly allocation. And I assumes that the start and end dates of the contract are always at the end of the month. If this is not the case, you may need to adjust the formula accordingly. Also, this formula does not account for multi-year deals where you need to show the last month to calculate 50% of year 1 and 50% of year 2. You might need to add additional logic to handle this scenario.
    I hope this helps! Let me know if you have any other questions.

Resources