Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kjel
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
johnt75
Super User
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
kjel
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.

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.