Forum Discussion

Andiko's avatar
Andiko
Frequent Visitor
1 year ago
Solved

Problem with DateADD, Running Totals and Dates

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

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

4 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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's avatar
      Andiko
      Frequent Visitor
      1 year ago

      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's avatar
        shafiz_p
        Super User
        1 year ago

        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.