Allocating values to a time period (month, years, etc. ) based on a range of dates

Hello, newbie to Airtable. I am trying to build a CRM tool that also forecasts future period recurring revenues.

We have projects that often span 2-3 years. I would like to allocate the future revenue (equal monthly amounts is fine) across 12/31/XX fiscal year time periods.

My inputs are:
Start date
End date
Monthly revenue

I’d like my outputs to be:

  • Prior Period Revenue (any revenue prior to this fiscal year)
  • Current Fiscal Year (e.g. 12/31/2020) Revenue
  • Next FY Revenue
  • Backlog Revenue (anything beyond next fiscal year)

Let’s assume a hypothetical project as follows:
Start date: July 1, 2019
End Date: March 31, 2022
Monthly revenue: $10,000

I would expect the following results:

  • Prior Period Revenue: $60,000
  • Current FY Revenue: $120,000
  • Next FY Revenue: $120,000
  • Backlog Revenue (anything beyond next fiscal year): $30,000

I have been able to build this in excel but would really like to manage this process in Airtable. But I am stumped with Airtable date formulae. I think it boils down to this: how do I allocate months in a date range to different fiscal years?

I appreciate any thoughts.

4 Likes