Forum Discussion
Recurring Revenue Measurement
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.
Thanks and regards
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
7 Replies
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 ResultThat did it! Thanks very much
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?
Ignore please, I saw and tried JohnT's suggestion and that worked. Thanks!
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 ResultThat worked, thanks!