Problem with DateADD, Running Totals and Dates

[Andiko]

Hello!

I have a Meassure called [Cumulative Recurring Revenue] which calculates the Cumulative Revenue Using the formula below:

CALCULATE([W_Revenue_Recurring],

	FILTER(
		ALLSELECTED('Calendar'[Date]),
		ISONORAFTER('Calendar'[Date], max([Close_Date]),DESC)
		)
)

I need to Shift all the values of the [Cumulative Recurring Revenue] by 1 year. This has been made in the Column [Cumulative Recurring Revenue - Shifted] and for that I have used the formula below:

CALCULATE([W_Revenue_Recurring],

	FILTER(
		ALLSELECTED('Calendar'[Date]),
		
		ISONORAFTER(
			DATEADD( 'Calendar'[Date],1,YEAR),
			MAX('Calendar'[Date]),	
			DESC
		)
	)
)

Now, the problem is that, as you can see in the image below, the [Cumulative Recurring Revenue] is giving me the values for the entries that exist in the InputDB. But, the [Cumulative Recurring Revenue - Shifted] is giving me the values for all the dates from the Calendar Table (even if they do not exist in the InputDB). 

The InputDB and the Calendar Table are related by the Close_Date Column

I have tried many options like calculating the cumulative recurring revenue in another way (it happens the same with the dates), using the EDATE function, SUMX, SUM, Etc. 

Nothing seems to work. Thus, I would like to get your help please!

I am attaching the file I am using here: Test-File

[shafiz_p · Accepted solution]

Hi @Andiko  Try update your measures using the below code:

Use ALL function instead of ALLSELECTED in Cumulative Recurring Revenue.

Cumulative Recurring Revenue =

CALCULATE(
        [W_Revenue_Recurring],
	FILTER(
		ALL('Calendar'[Date]),
		'Calendar'[Date] <= max(InputDB[Close_Date])
	)
)

And 

Cumulative Recurring Revenue - Shifted =

CALCULATE(
	[Cumulative Recurring Revenue],
        SAMEPERIODLASTYEAR('Calendar'[Date])
)

Output:

Hope this helps!!

If this solved your problem, Please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[Andiko]

Thank you very much @shafiz_p. It is very helpful!!

I have one more question: In case I need to shift the Cumulative Recurring Revenue by 1 Quarter or 1 Month, how would the formula change? 

Thanks a LOT for your help!

[shafiz_p]

Yes, you can change SameperiodLastYear to Date add. Here is the example:

Cumulative Recurring Revenue - Shifted=
CALCULATE(
	[Cumulative Recurring Revenue],
DATEADD('Calendar'[Date], -1,MONTH)
)

Now change Month, year, quarter, day etc according to your need.

Here is the desired output:

Hope this helps!!

If, please accept it as a solution and Kodus.

[Andiko]

@shafiz_p, it did work perfectly. Thank you so much!