MRR Calculation Based on single line Subsciption data

[BobKoenen]

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. 

[v-shex-msft · Accepted solution]

Hi @BobKoenen,

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

[BobKoenen]

Thankx! it works. A bit tricky when no months are selected but none the less thanks!