Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Spreading an Amount Over Time with Start and End Dates

The following shows various formulas that can be used to spread a total amount from a start date to an end date on a modeled sheet.  If no end date is provided, or it is prior to the start date, this example will assume a duration of 1 year (365 days, 12 months) and will ignore the day portion of the start date (treating the start month as a full month).  There is a series of accounts used to generate the result and there are two options for the result. 

Option 1, or Per Day

  • Works out the number of days between the start and end date then breaks the total value down to a per day value
  • Then spreads from the start to the end date based on the number of days in the month. 
  • Months with more days will receive a larger share of the spread
  • Mid month start/end dates will calculate correctly

Option 2, or Per Month

  • Works out the number of months between the start and end dates, then breaks the total value down to a per month value
  • Then spreads from the start to the end date with each month treated equally
  • Mid months start/end dates work as expected unless they are in February of a leap year, then total amount will be slightly off.

The following Modeled sheet is the basis for the examples:

clipboard_e237f566f310638e42c3f6c047e65eeaf.png

There are 3 columns that will be referenced in formulas:

  • ROW.Value
  • ROW.Start_Date
  • ROW.End_Date

This is a basic example that can easily be expanded on by adding dimensions.  The example sheet is included in this article so that you can optionally import it into your model to view it in action.

Steps for importing a modeled sheet

  1. Extract the XML file from the attached ZIP file   Formula_Samples.zip
  2. Navigate to Modeling -> Level Assigned Sheets (or User Assigned Sheets)
  3. Click Upload New Modeled Sheet
  4. Browse for the file and click Upload (this will create a sheet called Formula Samples)

Below are the accounts, their description, and their formulas:

Total Value

Description:  A redundant account as you could pull the value into other formulas right from the column in the sheet but it is common to pull it into an account so that is can be seen in reports and row details.

Formula: 

ROW.Value

Result:

clipboard_edf778083484ca9e389846f4b895dae5d.png

Month Fraction

Description:  This account will be used to both determine the months that will have data based on the start and end dates, and weight the first and last month if they don’t start on the 1st of the month.

Formula:

iff(

  # Check if the end date was provided and after start, otherwise assume 1 year

  isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date ,

  iff(

  this.version.positionof(this.month) >= this.version.positionof(ROW.Start_Date.month) and this.version.positionof(this.month) <= (this.version.positionof(ROW.Start_Date.month) + 11),

      1, #ignore the day part of the date and use Start month to Start month + 11

      0

   ),

   # Start and End dates provided

  timefraction(ROW.Start_Date, ROW.End_Date)

)

Result:

clipboard_e8179f431e7d02790aad2072358ba4c81.png

Number of Days

Description:  This account is used for Option 1.  It works out the number of days between the start and end dates or returns 365 if there is no end date, or the end date is before the start date.

Formula:

iff(

  # if the end date is blank or before the start date assume 1 year

  isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date,

  365,

  ROW.End_Date - ROW.Start_Date + 1

)

Result:

clipboard_e6580c8390cfb769c41a3796fdd16bd28.png

Number of Months

Description:  This account is used for Option 2.  It works out the number of months between the start and end dates by summing the Month Fraction determined above (you may need to adjust the time filter if your spread is greater than the 5 years allotted for).  It returns 12 if there is no end date, or the end date is before the start date.

Formula:

iff(

  # if the end date is blank or before the start date assume 1 year

  isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date,

  12,

  # Sum up the Month Fraction to get accurate base for Months

  ROW.Month_Fraction[time=this.year-2:this.year+3]

)

Result:

clipboard_ef9f72c10d7fb40d588f8a4c6bebecd23.png

Value Per Day

Description:  This account is used for Option 1.  It divides the Total Value by the Number of Days to get to a per day value.

Formula:

divf(ROW.Total_Value, ROW.Number_of_Days)

Result:

clipboard_e5861f8bcf230ee2f7c6df14c1fd77a55.png

Value Per Month

Description:  This account is used for Option 2.  It divides the Total Value by the Number of Months to get a per month value.

Formula:

divf(ROW.Total_Value, ROW.Number_of_Months)

Result:

clipboard_e504b55da7321eeabceecdca46071e212.png

Monthly Value From Per Day (Option 1)

Description:  This account will take the Value Per Day times the number of days in the month then times the Month Fraction to get to a value for each month from the start to the end date.

Formula:

# Use the daily value times the number of days in the month weighted by the month fraction

ROW.Value_Per_Day * this.month.numberofdays * ROW.Month_Fraction

Result:

clipboard_e438af08ff072565242d081375689c85d.png

Monthly Value From Per Month (Option 2)

Description:  This account will take the Value Per Month times the Month Fraction to get to a value for each month from the start to the end date.

Formula:

ROW.Value_Per_Month * ROW.Month_Fraction

Result:

clipboard_e8fb2c671715bfae439ac0a9c6b511d7a.png