3 Replies Latest reply on Feb 12, 2019 8:42 AM by Deepak Rai

    First day of last quarter

    Amyl

      Hi

       

      Can some one please help me how to get the first day of the last quarter.

       

      Example:

      Current date - 2/12/2019

      Last quarter = 2018'Q4

      First day of Last quarter = 10-1-2018

       

      Thank you

        • 1. Re: First day of last quarter
          Alyn Still

          You can do this by combining two functions - dateadd and datetrunc.

          Using dateadd - we can find out the date three months ago (i.e. within last quarter)

               dateadd('month',-3,[DateFieldName])

           

          Using datetrunc - we can truncate/round-down a date to giving the start of the period, in our case the start of the quarter. The format for that would be:

               datetrunc('quarter',[Some date])

           

          If you combine these - by passing the 'last quarter' date into datetrunc, you get:

               datetrunc('quarter',dateadd('month',-3,[DateFieldName]))

          • 2. Re: First day of last quarter
            Joe Oppelt

            Just to add to what Alyn suggested, there are times when  people want to know the LAST day of a quarter or a month.

             

            You can do the same thing Alyn did, but position yourself to the first day of the NEXT quarter (or month) and then simply subtract 1 from that.

             

            In your case you would only need to DATETRUNC to the current quarter, and then subtract 1.

             

            datetrunc('quarter',[DateFieldName]) -1

             

            That would give you the last day of the prior quarter.

             

            DATEADD('month', 1, DATETRUNC('month',[Date Field]))-1

             

            That would give you the last day of the current month.  (Truncate to the beginning of this month.  Add a month.  Subtract a day.)  Tableau date math is robust enough to get the right last day (30, 31, 28 or 29) based on what month is it and leap years and all.

            • 3. Re: First day of last quarter
              Deepak Rai

              Here it is:

              Thanks

              Deepak

              If it Helps, Pl Mark it Helpful and CORRECT to Close Thread