Forum Discussion

lcasey's avatar
lcasey
Post Prodigy
9 years ago

Calculated 2 years previous

Hello,

 

Does anyone know how I would write a correct formula?

 

Year 2 = CALCULATE([YTD Budget],ALLSELECTED('Calendar'[Year]) -2,ALL('Calendar'[Month]))

 

In the above formula I want to be able to select any year in the calendar table and have the formula go back 2 years in time and grab that entire years budget.

 

Adding the -2  breaks th eformula but you can see what I was trying to do. What should I be using?

 

Need Help

4 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    lcasey

     

    YTD From 2 Ago =
CALCULATE ( [YTD Budget], DATEADD ( 'Calendar'[Date], - 2, YEAR ) )

    If [YTD Budget] uses TOTALYTD - then the above will give you the YTD Total but from 2 years ago! :smileyhappy:

    • lcasey's avatar
      lcasey
      Post Prodigy
      9 years ago

      Thanks,

       

      My YTD budget is useing:

       

      YTD Budget = IF(SUM('00-GLSummary'[Revenue]) > 0,  Abs(SUM('00-Budget'[BUDGETAMT])),sum('00-Budget'[BUDGETAMT]))

       

      So this formula is blank for now. I am going to try and convert my YTD Budget to use TotalYTD

       

      That sounds like a more sdtandard way to calculate amounts anyway.

      • lcasey's avatar
        lcasey
        Post Prodigy
        9 years ago

        Weird,

         

        I changed my formula to:

         

        IF(SUM('00-GLSummary'[Revenue]) > 0,  TOTALYTD(Sum('00-Budget'[BUDGETAMT]),'Calendar'[Date]),sum('00-Budget'[BUDGETAMT]))

         

        and the YTD From 2 Ago = CALCULATE ( [YTD Budget], DATEADD ( 'Calendar'[Date], - 2, YEAR ) )

         

        is still blank.