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 abl...
Need Help
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.

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi lcasey

         

        Just to confirm that you do not have any filters in the Visual, Page or Report levels?

         

        What I would suggest to troubleshoot, is to create a table and put the indivudual measures into the table with your dates. In doing so this can help visually see where the possible error is occuring.

         

        I have done that in the past, which has helped me identify where I was going wrong.