Help me improve this recurring revenue measure

[kjel]

I have created the following measure to calculate recurring revenue:

Maintenance Amount = 

VAR MinDate = MAX(SELECTEDVALUE('Recurring Revenue'[Recurring Start Date]),MIN('Date'[Date]))
VAR MaxDate = MIN(SELECTEDVALUE('Recurring Revenue'[Recurring End Date]),MAX('Date'[Date]))
VAR MinMonth =
    IF(MinDate > MaxDate,BLANK(),
        DATEDIFF(
            MinDate,
            MaxDate,
            MONTH
        ) + 1
    )
RETURN
    SUMX(
        'Recurring Revenue',
        MinMonth * 'Recurring Revenue'[Monthly Recurring Amount]
    )

Basically, I want to do a row-wise calculation in my "Deferred Revenue" table based on the current date filter context.

My situation is that I want to be able to see the recurring revenue even if the invoice date (the field used to relate to the date table) is outside of the current filter context, I have gone around this by creating a calculated table specifically for deferred revenue. The measure currently works in a very limited context: if I create a table with the Start Date and the End Date it will give me the correct total for a given row. However, it will not calculate the grand total and it appears blank if I put it into a column visual. How can I fix this?

It would be incredibly helpful if anyone has an idea on how to accomplish this without using a separate table for the recurring revenue. I have the same columns in the "Revenue" table so I could simply choose to only calculate the rows that have recurring revenue if it were possible to ignore the current filter context.

[johnt75]

Try

Maintenance Amount =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    CALCULATETABLE ( VALUES ( 'Date'[Year month] ), 'Date'[Date] <= MaxDate )
VAR Result =
    SUMX (
        DatesToUse,
        VAR EndOfMonth =
            CALCULATE ( MAX ( 'Date'[Date] ) )
        RETURN
            CALCULATE (
                SUM ( 'Revenue'[Monthly Recurring Amount] ),
                'Date'[Date] <= EndOfMonth
            )
    )
RETURN
    Result

[kjel]

Hi,

Thanks for the response, it did not work for me because the start/end date logic was not correctly implemented and revenue with a start date in the future got included in the calculation, but I think it's on the right track.

I will look into using the CALCULATETABLE function, that might be what I'm missing.