Forum Discussion
Recurring Revenue Measurement
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 ResultI 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 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 ResultGreg_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?
Ignore please, I saw and tried JohnT's suggestion and that worked. Thanks!
Lodan You can use ALLSELECTED instead of ALL to preserve the filters.