Forum Discussion
Monthly Recurring Revenue counted over multiple months
Am looking for a formula for the following. Customer x order 1 product but is paid over 8 months from January to August. Rather an just giving me the sum of the product for the full 8 months i want to be able to look at the MRR but when i filter to January Febuary and March the MRR is * by 3 as it is looking at 3 months. Currently i do this with excel but have multiple columns for month which is a huge headache when looking at 10 years worth of data. Any help would be amazing.
2 Replies
v-yuta-msftCommunity Support
Hi dataman123,
Could you share some sample data and clarify more details about your expected result?
Regards,
Jimmy Tao
Hi
I hope this post is still being monitored since I've got a similar (I think) scenario. What I would like to project is MRR and ARR based on subscription begin and end dates and in categories "inforce Business" and "Prospective Business".
Example:
Category Name Licence Start Licence End Yearly Amount Monthly Amount in force Cust 1 01/01/2021 31/12/2021 1200 100 in force Cust 2 15/03/2021 14/03/2022 2400 200 in force cust 3 15/05/2021 14/05/2022 3600 300 prospect cust 4 01/04/2021 31/03/2022 3600 300 prospect cust 5 01/05/2021 30/04/2022 600 50 Renewal_Prospect cust 1 01/01/2022 31/12/2022 1200 100 Renewal_Prospect cust 2 15/03/2022 14/03/2023 2400 200 Renewal_Prospect cust 3 15/05/2022 14/05/2023 3600 300 Desired outcome:
In excel this can be helped by creating a table that shows the total for each month in the three (or better x) different categories and then running a stacked bar chart over it. Hopefully in power BI there's a simpler solution?
Any help would be much appreciated.
Thanks
