How to calculate Monthly Recurring Revenue using DAX?

Question

My end goal is to have a Monthly Recurring Revenue for a subscription service. Example:

Rules:

• Contract column must be different than 0
• The following table is already related to the calendar dimension using another column.

CRMTable

Contract	ContractDate	ContractEnd	Revenue
X	01/01/2023	01/06/2023	1000
0	01/02/2023	01/07/2023	1000
X	01/03/2023	01/08/2023	1000
X	01/04/2023	01/09/2023	1000
X	01/05/2023	01/10/2023	1000

The goal is to have this result:

| Month | Revenue | | ----- | ------- | | 01/2023 | 1000 | | 02/2023 | 1000 | | 03/2023 | 2000 | | 04/2023 | 3000 | | 05/2023 | 4000 | | 06/2023 | 3000 | | 07/2023 | 3000 | | 08/2023 | 2000 | | 09/2023 | 1000 | | 10/2023 | 0 | ...

And then display it in a line graph. But I don't know how to calculate this. Already used chatGPT but it didn't help much.

Thanks in advance!

I tried using this but it doesn't add monthly.

MRR = CALCULATE(SUM(CRMTable[Revenue]), CRMTable[Contract] <> "0")

Answer 1

I have developed this based on an unrelated, single-column table which comprises the first day of each month, e.g. 01/01/2023, 01/02/2023, etc. You can then create a Pivot Table using these months together with the following measure:

=
VAR ThisDate =
    MIN( Months[Month] )
RETURN
    SUMX(
        FILTER(
            CRMTable,
            CRMTable[Contract] <> "0"
                && CRMTable[ContractDate] <= EOMONTH( ThisDate, 0 )
                && CRMTable[ContractEnd] > ThisDate
        ),
        CRMTable[Revenue]
    )