3 Replies Latest reply on Aug 9, 2017 9:39 AM by Renato Lyke

    Previous Year YTD

    Renato Lyke

      Hi,

      MY Fiscal Year Starts from July. Below is the calculation that i have for current YTD.

       

      IF

      (

      [CREATE Date]<=today() AND MONTH(today())>=7 AND YEAR(today())=YEAR([CREATE Date])

      AND MONTH([CREATE Date])>=7

      )

      OR

      (

      [CREATE Date]<=today() AND MONTH(today())<7 AND

      (

      YEAR(today())= YEAR([CREATE Date]) AND MONTH([CREATE Date])<7

      OR

      YEAR(today())-1=YEAR([CREATE Date]) AND MONTH([CREATE Date])<=7

      )

      )

      THEN [Number of Records] END

       

      I am trying to get the previous YTD.

       

      My Create date is used as a filter. I dont wish to use a parameter to create the same.

       

      Any suggestion how to get the previous year YTD.

       

      Regards,

      Renato.

        • 1. Re: Previous Year YTD
          Karthik Venkatachalam

          I would duplicate the entire measure and put DATEADD('year', -1, CREATEDATE) function in lieu of (CREATE DATE) and do the same for TODAY() as well.

          • 2. Re: Previous Year YTD
            Renato Lyke

            Hi Karthik,

             

            I tried the same.

             

            DATEADD('year',-1,[CREATE Date])<=DATEADD('year',-1,today()).

             

            I repeated this where ever [Create Date] and Today is present it did not work.

             

            Regards,

            Renato.

            • 3. Re: Previous Year YTD
              Renato Lyke

              Infact i tried this to make it even more generic where if my date is selected to the year say 2016 to give me 2015 values.

               

               

              It works fine for current YTD when i replaced today with Create date to get current year values based on the filter.

               

              The problem is the previous year also gives the current year values even after using DATEADD

               

               

              This is what i am using for Previous Year YTD.

              IF

              (

              DATEADD('year',-1,[CREATE Date])<=DATEADD('year',-1,[CREATE Date] )

               

               

              AND MONTH(DATEADD('year',-1,[CREATE Date]))>=7

               

               

              AND YEAR(DATEADD('year',-1,[CREATE Date]))

               

               

              = YEAR(DATEADD('year',-1,[CREATE Date]))

               

               

              AND MONTH(DATEADD('year',-1,[CREATE Date]))>=7

              )

              OR

              (

              DATEADD('year',-1,[CREATE Date])

               

               

              <=DATEADD('year',-1,[CREATE Date])

               

               

              AND MONTH(DATEADD('year',-1,[CREATE Date]))<7 AND

              (

              YEAR(DATEADD('year',-1,[CREATE Date]))=YEAR(DATEADD('year',-1,[CREATE Date]))

               

               

              AND MONTH(DATEADD('year',-1,[CREATE Date]))<7

               

               

              OR

               

               

              YEAR(DATEADD('year',-1,[CREATE Date]))-1=YEAR(DATEADD('year',-1,[CREATE Date]))

               

               

              AND MONTH(DATEADD('year',-1,[CREATE Date]))<=7

              )

              )

              THEN [Number of Records] END