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('C...
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

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.


      • Andiko's avatar
        Andiko
        Frequent Visitor
        1 year ago

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