cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

2 REPLIES 2
Super User

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
``````
Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.