topic Re: Help me improve this recurring revenue measure in DAX Commands and Tips

Help me improve this recurring revenue measure

kjel — Tue, 27 Jun 2023 08:29:10 GMT

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.

Re: Help me improve this recurring revenue measure

johnt75 — Tue, 27 Jun 2023 09:28:27 GMT

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

Re: Help me improve this recurring revenue measure

kjel — Wed, 28 Jun 2023 10:03:31 GMT

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.