How can I show total recurring revenue per year, but only if there is no cancellation date filled?

Question

I'm trying to build a report based on an excel sheet that has (amongst others) the following columns:

• recurring revenue: this columns show a certain amount (FI € 500,-) per customer, which is billed every year.

• cancellation date: this column shows a date if the customer has cancelled the subscription. FI: if the date 01-06-2020 is filled, the recurring revenue should be counted in the total of 2020, but not anymore in 2021.

Does anyone have a formula for how I can show this in a visual? I tried making a new calculated column with the following formula, but this only shows the recurring revenue for the lines in the table that have a cancellation date filled:

Recurring revenue=

var currentdate = SELECTEDVALUE('Date S&A'[Date]) return CALCULATE(SUM('Licentie & SA'[recurring revenue),'Licentie & SA'[cancellation date] > currentdate )

Some sample data and expected output:

Ok, so some sample data would be (sorry not sure how to format it correctly here):

Customer	Date start	Cancellation date	Recurring revenue
1	1-10-2012	1-6-2015	€ 816,00
2	1-11-2014	1-11-2018	€ 2.645,00
3	1-11-2014		€ 1.690,50

The expected output would be:

Year	Total recurring revenue
2012	€ 816,00
2013	€ 816,00
2014	€ 5.151,50 (816,00 + 2.645,00 + 1.690,50)
2015	€ 5.151,50
2016	€ 4.335,50 (5.151,50 - 816, cause customer 1 cancelled in 2015)
2017	€ 4.335,50
2018	€ 4.335,50
2019	€ 1.690,50 (4.335,50 - 2.645,00)

Answer 1

The first step is creating a calendar table based on the data you have supplied.

Calendar Table

I've ended the calendar in 2019 to simulate the output you have provided.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date start] ), DATE ( 2019, 12, 31 ) ),
    "Year", YEAR ( [Date] )
)

Total Recurring Revenue

From there, you can calculate the total for each year with the following calculation. The main idea here is using SUMX to iterate each row of Table to calculate the sums. The logic to filter each row is inside CALCULATE. It's important to highlight this will only work for years.

Total Recurring Revenue = 
VAR _CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    SUMX (
        'Table',
        CALCULATE (
            SUM ( 'Table'[Recurring revenue] ),
            _CurrentYear >= YEAR ( 'Table'[Date start] )
                && (
                    _CurrentYear <= YEAR ( 'Table'[Cancellation date] )
                        || ISBLANK ( 'Table'[Cancellation date] )
                )
        )
    )

Ouput

To display the same output, you need to use Year from Calendar and the measure above.