2 Replies Latest reply on Mar 5, 2017 9:40 PM by sneha simha


    sneha simha

      Dear All


                                  I want to calculate YTD for last years say from 2012 till date.Kindly help me with the formulae to achieve the same.However YTD for the current and Previous years have been achieved using the formulas


      if [ Date]>=DATE(STR(if MONTH(TODAY()-1)>3

      then YEAR(TODAY()-1) else YEAR(TODAY()-1)-1 end)+"-04-01")   (Current Year)


      [ Date]<=TODAY()-1 then [sales]



      if [ Date]>=DATE(STR(if MONTH(DATEADD('year',-1,TODAY()-1))>3

      then YEAR(DATEADD('year',-1,TODAY()-1))

      else YEAR(DATEADD('year',-1,TODAY()-1))-1 end)+"-04-01")                   (Previous Year)


      [Date]<=DATEADD('year',-1,TODAY()-1) then [sales] end




      Sneha simha

        • 1. Re: YTD
          Norbert Maijoor

          Hi Sneha,


          Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.



          1. YTD CY: if DATEDIFF('year',[date],TODAY())=0 and DATEDIFF('month',[date],TODAY())>=0 then [valeu] END


          2. YTD CY-1: if DATEDIFF('year',[date],TODAY())=1 and DATEDIFF('month',[date],TODAY())>=12 then [valeu] END

          3. YTD CY-2 :if DATEDIFF('year',[date],TODAY())=2 and DATEDIFF('month',[date],TODAY())>=24 then [valeu] END



          • 2. Re: YTD
            sneha simha

            Hi Norbert


                                          M able to get only last 2 years sales,the same is shown below.

            I used the same formula given above and replaced the number from 24 to 36 and so on.

            Kindly Help me.



            Sneha simha