Monthly Recurring Revenue - Running Monthly Total

[samdep]

Hi Community,

I would like to build a chart that shows our total MRR by month - the problem is, of course, the chart assumes that the revenue is not recurring, rather it's tied to a single month.

Sample Data:

Customer ID    Created Date    Last Modified Date   Amount

123                  1/1/2021           2/1/2021                  $5

456                  1/15/2021         2/15/2021                $10

789                  2/1/2021           3/1/2021                  $20

The 'Created Date' is the start of the customer's subscription and the 'Last Modified Date' reflects the last payment we received from each customer. Graphing this as is with 'Created Date' tied to 'Date' in my Calendar table, the bars/line chart would show $15 in revenue for Jan 2021 and $20 for for Feb 2021.

My goal, however, is to show that MRR running total by month -- so, as opposed to the above, it would be $15 for Jan 2021 and $35 for Feb 2021 and so on... Any DAX measure tips for accomplishing this with the two date fields I have?

Thank you!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu · Accepted solution]

@samdep 

does that mean you the sum only based on create date?

please see the attachment below

[samdep]

@ryan_mayu - Thank you so much! This achieves a running total of my MRR. One follow-up that I should have asked before : ) -- If customer 123 cancels before their third payment date on March, any idea how I can include Customer 123 in the running total in Jan, Feb, but exclude them from March - given that they've cancelled their subscription. Thanks again!! 

Customer ID    Created Date    Last Modified Date   Amount   Status

123                  1/1/2021           2/1/2021                  $5            Cancelled

456                  1/15/2021         2/15/2021                $10          Active

789                  2/1/2021           3/1/2021                  $20          Active

[ryan_mayu]

@samdep 

pls see the attachment below.