2 Replies Latest reply on Oct 23, 2018 9:21 AM by Tu Duong

    Calculate average last 3 months?

    Tu Duong

      Hi community member,

       

      I am struggling to find a way to show the sales number together with its last 3-month average. For example, If a reader chooses "Reporting month"- a parameter-  Sep 2018, I want to show two numbers:

       

      • Sales number for Sep 2018
      • Average sales number for 3 months BEFORE Sep 2018 (which is the average of Jun, Jul, Aug 2018)

       

      And if the reader chooses October, November etc...then the numbers will be changed accordingly.

       

      I wish it looks like this:

       

       

      I have tried:

       

      1. Table calculation. I can calculate the average, However, I have to show all the past months so it does not look clean as I wanted. I can hide the past month columns, but when the reader selects different report months, then it messes up the format.

      2.  LOD calculation. I can also calculate the average, but again, I have to show the path months. Not clean.

       

      Is there a way to get what I wished for?

       

      Attached is my sample workbook using Superstore with my attempts. I only added:

       

      • a Report month parameter,
      • "Is Report month?" logic test
      • a calculated field with LOD
      • a calculated field with Table calculation

       

      I have Tableau 2018.1.

       

      Thank you!

        • 1. Re: Calculate average last 3 months?
          Zhouyi Zhang

          Hi, Tu

           

          Please find my solution attached as well as below screenshot for your reference.

           

          Hope this helps

           

          ZZ

          • 2. Re: Calculate average last 3 months?
            Tu Duong

            Thanks Zhouyi. This works - although I had to make some modifications! It's a smart solution to create the measure "Current Month Sales" and not put Order Date as the filter.

             

            For those of you who are looking for an answer, please note that the measure "last 3 month Avg" uses FIXED DATETRUNC('month', [Order Date]). IF you have other dimensions on the filter shelf, those dimensions won't be used as filter due to the FIXED statement, and therefore, the number shown will be incorrect.

             

            For me, as I only have one value each month, I just added the AVG formula directly to the "last 3-month Total" measure to get the results I am looking for.