Thank you in advance for the help.
I have data in which each row is a contract line. It has the contract start date, end date, created date and contract value. My company realizes contract revenue on a monthly amortized basis. As in, a 1 year contract booked on (or before) 01JAN2020 for $12,000 with contract line start date of 01JAN2020 and end date of 31DEC2020 will yeild $1,000 in revenue for each of the 12 months in 2020.
However, if the contract is booked after the start month, the back revenue is realized in the month it is booked. for the example above, if I booked the same contract on 01FEB2020 than the February revenue would be $2,000 and the remaining months would be $1,000.
I'd like to create a work flow which converts my report of contract lines into a monthly amortization report.
Input-
| Contract Number | Contract Line number | Start Date | End Date | Created Date | Amount |
| 123345 | 10 | 1/1/2020 | 12/32/2020 | 1/1/2020 | 12000 |
| 12346 | 10 | 1/1/2020 | 12/32/2020 | 2/1/2020 | 12000 |
| 12347 | 10 | 1/1/2020 | 12/32/2020 | 12/1/2019 | 12000 |
Desired output-
| Contract | Line | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 |
| 123345 | 10 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
| 12346 | 10 | 0 | 2000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
| 12347 | 10 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
Thank you for the help.
Also, the list of contracts will overlap time frames. so, it will need to return a 0 for revenue during a month the contract is not active of course.
