Hey guys,
I got request to calculate billing amounts of our customers based on the monthly payments. Please check example table:
| Customer Name | Monthly Amount | Payment Start date | Payment End date |
| AAA | 100 | 1.1.2023 | (blank) |
| BBB | 150 | 1.3.2023 | 31.7.2023 |
| CCC | 300 | 1.8.2023 | 31.3.2024 |
| DDD | 50 | 15.9.2023 (future date) | (blank) |
The result should be following table (it can be measure or calculated table - no preference). I do not prefer Power Query (due to lack of knowledge and issue with maintenance of calculation). It must calculate the monthly billing amounts from the year of Payment Start date until the end of next calendar year (current year +1). Payment end date can be blank which means not defined payment interval.
| Month | Billed amount |
| January 2023 | 100 /* new customer AAA, calculation starting from the year of first payment start date */ |
| February 2023 | 100 |
| March 2023 | 250 (100+150) /* new customer BBB */ |
| April 2023 | 250 (100+150) |
| May 2023 | 250 (100+150) |
| June 2023 | 250 (100+150) |
| July 2023 | 250 (100+150) /* expiration of customer BBB */ |
| August 2023 | 400 (100+300) /* new customer CCC */ |
| September 2023 | 450 (100+300+50) /* new customer DDD */ |
| October 2023 | 450 (100+300+50) |
| November 2023 | 450 (100+300+50) |
| December 2023 | 450 (100+300+50) |
January 2024 | 450 (100+300+50) |
| February 2024 | 450 (100+300+50) |
| March 2024 | 450 (100+300+50) /* expiration of customer CCC */ |
| April 2024 | 150 (100+50) /* only customer AAA and DDD */ |
| ..... | |
| December 2024 | 150 (100+50) /* end of next calendar year */ |
Hope it is clear from the desired table.
Thank you for any help!
IvanS
