Create future revenue model for subscriptions MRR

[BobKoenen]

Hi All,

I have a data table with subscriptions in it. 

Start date	End date	Amount	Frequency	Total Amount	Customer
1-1-2021	31-12-2021	100	Month	1200	A
1-12-2020	28-2-2021	500	Month	1500	B
1-3-2021	31-8-2021	1000	Quarterly	2000	C
1-4-2021	31-4-2021	3000	Once	3000	D
1-6-2021	31-6-2021	2000	Year	2000	E
1-1-2021	31-12-2021	100	4 Weekly	1300	F

I want to create an month by month overview of the amounts as below

Month	To be invoiced
1	(A = 100) (B = 500) (F= 100) = 700 euro
2	(A = 100) (B = 500) (F= 100) = 700 euro
3	(A = 100)  (F= 100) (C = 1000)= 1200 euro
4	(A = 100)  (F= 100) (D = 3000)= 3200 euro
5	(A = 100)  (F= 100)= 200 euro
6	(A = 100)(C=1000)  (F= 100) (E = 2000) = 3200 euro

The rules are:

Month amounts are invoiced every month beginning on [start date] ending on [end date]

Quarterly    are invoiced beginning on [start date] and every 3 months after that until [end date]

Year & Once are invoiced in month of [start date]

4 weekly is invoiced from start date til end date with intervals of 4 weeks (I am willing to compromise and treat 4 weeks as month)

Hope you can help me

[MFelix · Accepted solution]

Hi @BobKoenen ,

Try to add the following measure:

TOTAL = 
VAR Monthly_value =
    SUMX (
        FILTER (
            CROSSJOIN (
                CALCULATETABLE (
                    FILTER ( 'Table'; 'Table'[Frequency ] IN { "Month"; "4 Weekly" } )
                );
                'Calendar'
            );
            'Calendar'[Date]
                = DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Calendar'[Date] ); DAY ( 'Table'[Start date] ) )
                && 'Calendar'[Date] >= 'Table'[Start date]
                && 'Calendar'[Date] <= 'Table'[End date]
        );
        'Table'[Amount ]
    )
VAR Yearly_Amount =
    SUMX (
        FILTER (
            CROSSJOIN (
                CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Year" ) );
                'Calendar'
            );
            'Calendar'[Date]
                = DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) )
                && 'Calendar'[Date] >= 'Table'[Start date]
                && 'Calendar'[Date] <= 'Table'[End date]
        );
        'Table'[Amount ]
    )
VAR Once_Amount =
    SUMX (
        FILTER (
            CROSSJOIN (
                CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Once" ) );
                'Calendar'
            );
            'Calendar'[Date] = 'Table'[Start date]
        );
        'Table'[Amount ]
    )
VAR Quartely_Amount =
    SUMX (
        FILTER (
            CROSSJOIN (
                CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Quarterly" ) );
                'Calendar'
            );
            'Calendar'[Date] >= 'Table'[Start date]
                && 'Calendar'[Date] <= 'Table'[End date]
                && 'Calendar'[Date]
                    IN {
                        DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) );
                        DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 3; DAY ( 'Table'[Start date] ) );
                        DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 6; DAY ( 'Table'[Start date] ) );
                        DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 9; DAY ( 'Table'[Start date] ) );
                        DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 12; DAY ( 'Table'[Start date] ) )
                    }
        );
        'Table'[Amount ]
    )
RETURN
    Monthly_value + Quartely_Amount + Once_Amount + Yearly_Amount

Be aware that if the start date of the contract is on the last day of the month you may need to adjust the formulas since some dates may not appear based on the date.

See result below:

Based on my test should work even with end dates higher than 1 year, that is why I made the quartely to only finish in 2025.

Has you have refered I have consider the 4 weeks in the month payments

PBIX attach.

[BobKoenen]

Thankx Alot. This is awesome and works really good. !

[lbendlin]

Assuming that you have a calendar table.

Use GENERATE() or GENERATESERIES() to create temporary tables for each of the subscriptions and their rules, and then add the result up for the respective months.

For your "4 weeks" logic you will also need to specify what you mean by week (start day, duration if different from 7 days etc)

You have an error in your sample for subscription E. End date should be 2022-05-31 or later. Similar, there is no April 31 for D.