3 Replies Latest reply on Dec 14, 2017 2:27 PM by ShivaRam Chennapragada

    Trailing 12 Months Calculation For Past 8 Years

    ShivaRam Chennapragada

      Hello Tableau Community, I have a dashboard that shows totals of different Metrics on Calendar Year basis for trailing 8 years. But I have received a request to modify the view in such a way that it displays totals for trailing 12 months for past 8 years(this helps in additional insights into the metrics). For confidentiality purposes I am unable to share the workbook, apologies!

       

      The layout I have currently is this,

      "Current EBITDA" is the previous calculation that I have - which calculates the totals on Full Calendar Year basis.

      "TTM" - is the one I am trying to achieve the Trailing 12 Months metrics. As you can see right now I get same values for both.

      This is my TTM caluclation:

      IF

      (DATEDIFF('month',[Accounting Period],DATE(DATEPART('month', TODAY()))))<=12

      THEN [Current]

      ELSE NULL

      END

       

      When I modify my TTM calculation to this,

      IF (DATEDIFF('month',[Accounting Period],TODAY()))<=12 THEN [Current] ELSE NULL END

       

      My view looks like this,

       

       

      I hope this information helps. Any ideas or corrections will be appreciated.

       

      Thanks.

        • 1. Re: Trailing 12 Months Calculation For Past 8 Years
          Sujay Paranjpe

          Hi Shiva,

           

          Just want to clarify the training 12 months definition in your context.

           

          If you are in December 2017 and assuming your (financial) year starts in January you would want Year to Date total for 2017 in TTM. In this case, the previous years total would not change at all? or am I misunderstanding the requirement.

           

          Best,

          Sujay

          • 2. Re: Trailing 12 Months Calculation For Past 8 Years
            Joe Oppelt

            ShivaRam Chennapragada wrote:

             

            ...  For confidentiality purposes I am unable to share the workbook, apologies!

             

             

            We don't need your exact data.  An excel-based workbook with 8 years of data with two columns:  [Date] and [Value] would be enough to demonstrate what you have and what someone can do with it.

             

            Anonymize your Tableau Package Data for Sharing

             

            It would help me see how your dates are set up, for example.  (Exact dates?  Month-and-year only?  Etc .)  And how your sheet is set up.  We're going to need table calcs to look back 12 months, for example.  And those often depend on what other dimensions are in play on the sheet.

             

            To get you started, though, if you have dates that include the calendar day of the month, you can simplify the data using DATETRUNC('month',[your date field]).  This truncates all dates to the first day of the respective month.  Sometimes this makes calcs on date ranges a whole lot easier if you are working at the month level.

             

            WINDOW_SUM(SUM([Current]), -11,0) will get you the prior 11 months plus the current month (for a total of 12 months) if your table is partitioned by month.  Or WINDOW_SUM(SUM([Current]), -12,-1) will get you the 12 month before the current month.

            • 3. Re: Trailing 12 Months Calculation For Past 8 Years
              ShivaRam Chennapragada

              Hi Sujay, because December is not ended yet, in my context trailing 12 months would be going back 12 months from "last completed month" i.e., November. So it would be from Dec, 2016 through Nov, 2017 (both inclusive). This would be same for all the years going back. If I'm in January 2018 then yes, my trailing 12 months would be one full year(2017) but when I move into Feb - it would be Feb 2017 - Jan 2018. Hope this gives you clarification.

               

              Thanks,

              Shiva.