Hello all, if I want to calculate the monthly recurring revenue, what would be the formula for that. For example I have the table revenue_data for subscriptions that can be for 1 month or 3 or 12 with the following columns:
Start Date, End Date, Number of months (between the two dates), Revenue
| start date |
end date |
number of months |
revenue |
| 05/05/2021 |
05/08/2021 |
3 |
300 |
| 05/05/2021 |
05/05/2022 |
12 |
1200 |
| 05/05/2021 |
05/06/2021 |
1 |
100 |
In this example if I would select June 2021 I would expect to have a revenue of 200. Basically the revenue from the first two entries only.
In excel it would be something like this:
=IF(AND('start date'<='selected date';'end date'>'selected date');(revenue/'number of moths');0)
