Recurring Revenue Measurement

[Lodan]

Hello,

I am a bit stuck.  The worst thing is I have done this before and it worked so not sure what I am doing wrong now.

Effectively for each month I wanted to sum up the revenue for that month.

However this includes recurring revenue.  So I want to include this as long as the date is between the 'completed' date and the 'end of term' date.  It doesn't seem to want to do that at the moment.

Any help would be appreciated please.

SD Help.pbix

Thanks and regards

[Greg_Deckler · Accepted solution]

@Lodan Try the following. I believe you used MaxDate twice whereas the second time it should have been MinDate. You also waaaaaay overused CALCULATE. 

MRR Revenue NC = 
VAR MaxDate = MAX( 'Date2'[Date] )
VAR MinDate = MIN( 'Date2'[Date] )
VAR __Table = FILTER( ALL( 'CMD Items' ), [Completed Date] <= MaxDate && [End of Term] > MinDate )
VAR Result = SUMX( __Table, [MRR$] )
RETURN Result

[johnt75 · Accepted solution]

I think you need to add REMOVEFILTERS( 'Date2' )

MRR Revenue2 = 
VAR MaxDate = CALCULATE( MAX( 'Date2'[Date] ) )
VAR MinDate = CALCULATE( MIN( 'Date2'[Date] ) )
VAR Result = CALCULATE(
	SUM( 'CMD Items'[MRR$] ),
    'CMD Items'[Completed Date] <= MaxDate,
    'CMD Items'[End of Term]> MaxDate,
    REMOVEFILTERS( 'Date2' )
)
RETURN Result

[Lodan]

That worked, thanks!

[Greg_Deckler · Accepted solution]

@Lodan Try the following. I believe you used MaxDate twice whereas the second time it should have been MinDate. You also waaaaaay overused CALCULATE. 

MRR Revenue NC = 
VAR MaxDate = MAX( 'Date2'[Date] )
VAR MinDate = MIN( 'Date2'[Date] )
VAR __Table = FILTER( ALL( 'CMD Items' ), [Completed Date] <= MaxDate && [End of Term] > MinDate )
VAR Result = SUMX( __Table, [MRR$] )
RETURN Result

[Lodan]

@Greg_Deckler Actually didn't quite fix it.  It worked as it was but when I then try to add a visual filter nothing happened.  I beleive the 'all' was stopping that but if i remove the 'all' it goes back to only counting the MRR against 1 month again.

Any ideas please?

[Greg_Deckler]

@Lodan You can use ALLSELECTED instead of ALL to preserve the filters.

[Lodan]

Ignore please, I saw and tried JohnT's suggestion and that worked.  Thanks!

[Lodan]

That did it!  Thanks very much