Hey guys, I got request to calculate billing amounts of our customers based on the monthly payments. Please check example table: Customer NameMonthly AmountPayment Start datePayment End dateAAA1001.1.2023(blank)BBB1501.3.202331.7.2023CCC3001.8.202331.3.2024DDD5015.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. MonthBilled amountJanuary 2023100 /* new customer AAA, calculation starting from the year of first payment start date */February 2023100March 2023250 (100+150) /* new customer BBB */April 2023250 (100+150)May 2023250 (100+150)June 2023250 (100+150)July 2023250 (100+150) /* expiration of customer BBB */August 2023400 (100+300) /* new customer CCC */September 2023450 (100+300+50) /* new customer DDD */October 2023450 (100+300+50)November 2023450 (100+300+50)December 2023450 (100+300+50)January 2024450 (100+300+50)February 2024450 (100+300+50)March 2024450 (100+300+50) /* expiration of customer CCC */April 2024150 (100+50) /* only customer AAA and DDD */..... December 2024150 (100+50) /* end of next calendar year */ Hope it is clear from the desired table. Thank you for any help!IvanS

Helper V

3 years ago

Solved

Future Monthly Recurring billing

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 */