MRR Running Total with DAX

Question

I have a table of MRR data where there is only a subscription start date and end date (end date is blank if the sub is still active).

With that in mind, how can I calculate, via DAX, the proper MRR totals by month for a customer who, for example, was active from Jan - March, but whose record now indicates that they've cancelled. I want to include that customer's recurring subscription fee in Jan, Feb and March -- but not April-August. And all as a running total?

Sample Data:

Customer ID	Sub Start Date	End Date	Monthly Amount	Status
Customer A	1/1/2021	3/1/2021	$5	Cancelled
Customer B	2/1/2021		$5	Active
Customer C	3/1/2021		$10	Active
Customer D	1/1/2021	8/1/2021	$20	Cancelled
Customer E	4/1/2021	6/1/2021	$50	Cancelled
Customer F	5/1/2021		$5	Active
Customer G	2/1/2021	7/1/2021	$10	Cancelled

So, in Feb, for example, the MRR total would be $30 (Customer A, B, D) and for April, it would be $95 (Customer B, C, D, E and G).

Once we get to August, for example, the total would be $40 (Customer B, C, D - Ds last month of activity, and Customer F).

Any idea of how to accomplish this either via new columns or DAX measures would be greatly, greatly appreciated - as for the life of me, I cannot find an example of this and I figure somebody else's MRR data is set up this way.

Answer 1

If you have a Calendar table connected to this one from example, then we can use this approach. Here we use GENERATE to create a virtual table for each day (based on range Start/End):

MRR = 
var temp = SELECTCOLUMNS( GENERATE(VALUES('Calendar'[Date]), CALCULATETABLE(Sheet1, FILTER(ALL(Sheet1), [Date]>= Sheet1[Sub Start Date] && [Date] <= if (ISBLANK(Sheet1[End Date]), TODAY(), Sheet1[End Date]) )))
, "Day", [Date], "Amount", [Monthly Amount])
return
CALCULATE(sumx(temp, [Amount]))

Answer 2

You can achieve the running total by having a calendar table and with a measure as following

RunningTotal =
VAR _1 =
    ADDCOLUMNS (
        GENERATEALL (
            t,
            DATESBETWEEN (
                'Calendar'[Calendar_Date],
                t[Sub Start Date],
                IF ( t[End Date] = BLANK (), TODAY (), t[End Date] )
            )
        ),
        "test", IF ( DAY ( [Calendar_Date] ) = 1, [Monthly Amount], 0 )
    )
VAR _00 =
    FILTER (
        GENERATE (
            _1,
            SELECTCOLUMNS (
                'Calendar',
                "dt", 'Calendar'[Calendar_Date] + 0,
                "yr", 'Calendar'[Calendar_Year] + 0,
                "mo", 'Calendar'[Calendar_Month] + 0,
                "fd", 'Calendar'[First_Day_in_Month],
                "lt", 'Calendar'[Last_Day_in_Month]
            )
        ),
        [Calendar_Date] = [dt]
    )
VAR _000 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            _00,
            "ld",
                MAXX (
                    FILTER ( _00, EARLIER ( [yr] ) = [yr] && EARLIER ( [mo] ) = [mo] ),
                    [dt]
                )
        ),
        "rev", IF ( [ld] = [lt], [Calendar_Date], [lt] )
    )
VAR _2 =
    ADDCOLUMNS (
        _000,
        "test2", SUMX ( FILTER ( _1, EARLIER ( [Calendar_Date] ) >= [Calendar_Date] ), [test] )
    )
RETURN
    CALCULATE (
        MAXX ( FILTER ( _2, [rev] = MAX ( 'Calendar'[Calendar_Date] ) ), [test2] )
    )

The calculations are dependent on a calendar table with minimum following structure

Calendar_Date	Calendar_Year	Calendar_Month	First_Day_in_Month	Last_Day_in_Month
2021-01-01	2021	1	2021-01-01	2021-01-31
2021-01-02	2021	1	2021-01-01	2021-01-31
2021-01-03	2021	1	2021-01-01	2021-01-31