Hi All, I have a data table with subscriptions in it. Start dateEnd dateAmount Frequency Total AmountCustomer1-1-202131-12-2021100Month1200A1-12-202028-2-2021500Month1500B1-3-202131-8-20211000Quarterly2000C1-4-202131-4-20213000Once3000D1-6-202131-6-20212000Year2000E1-1-202131-12-20211004 Weekly1300FI want to create an month by month overview of the amounts as below MonthTo be invoiced1(A = 100) (B = 500) (F= 100) = 700 euro2 (A = 100) (B = 500) (F= 100) = 700 euro3 (A = 100) (F= 100) (C = 1000)= 1200 euro4(A = 100) (F= 100) (D = 3000)= 3200 euro5(A = 100) (F= 100)= 200 euro6 (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

Hi Anonymous , 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.

Anonymous

Not applicable

5 years ago

Solved

Create future revenue model for subscriptions MRR

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

Need Help

MFelix

5 years ago

Hi Anonymous ,

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.

Monthly_PAyment.pbix43 KB

3 Replies

MFelix

Super User

5 years ago

Hi Anonymous ,

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.

Monthly_PAyment.pbix43 KB

Anonymous
Not applicable
5 years ago
Thankx Alot. This is awesome and works really good. !

lbendlin
Super User
5 years ago
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.

Forum Discussion

Create future revenue model for subscriptions MRR

3 Replies

Related content

POWERBI Subscription

Fabric Subscription

getting blank screen while creating subscription to onedrive

SUM based on VAR value/ Subscription Revenue Total

Dynamic Subscription