Help me improve this recurring revenue measure
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.