Forum Discussion
MRR Calculation Based on single line Subsciption data
Hi,
I am trying to create a montly MRR overview where a measure calculates the sum amount of the subscriptions which are active during that month. Preferable also incorporating half months. Sow where startdate or enddate of subscription is on the 15th of the month it is 0,5 of month. Can also be (15/daysinmonth)* Amount. This is the data.
| Name | Startdate | enddate | Monthly amount |
| Customer A | 1-1-2020 | 30-6-2020 | 300 |
| Customer A | 1-7-2020 | 31-12-2020 | 500 |
| Customer B | 1-1-2020 | 31-12-2020 | 300 |
| Customer C | 1-2-2020 | 15-10-2020 | 650 |
New subscriptions are added eacht month.
I Am trying to figure something out where I create a list in the query editor from 1...12 with wich I could use as monthnum to check of a subscriptionline is active during that month, but how exactly?. Maybe it is also completely solvable in DAX.
Hope you can help me I am open to any ideas.
Hi Anonymous,
I'd like to suggest you to add an expand table to expand detail records of your table, then you can simply calculate the rolling total amount based on expanding the table.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
2 Replies
Hi Anonymous,
I'd like to suggest you to add an expand table to expand detail records of your table, then you can simply calculate the rolling total amount based on expanding the table.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng