Skip to main content
cancel
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

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
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