Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Frequent Visitor

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(),
        ) + 1
        '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.

Super User
Super User


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



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.

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors