6 Replies Latest reply on Aug 1, 2017 1:02 AM by Andrew Morgan

    Previous month and year data

    Andrew Morgan

      Hi Tableau Experts,

       

      I need your help. I am trying to create a view to display values for current month, previous month, and previous year of current month based on a filter selection. For example, If I select Jan 2017 from filter then it should three columns, one for current month value, one for previous month  value i.e. Dec 2016, and one column for previous year value i.e. Jan 2016.

       

      In my view I have two parameters. One to select dimension and other to select measure. Now, here based on my year/month filter selection values in the three columns should change as well it should take the measure selector parameter into account.

       

      Attached here the workbook created in v10.1

       

      Appreciate your help.

       

      Thanks

      A.M.

        • 1. Re: Previous month and year data
          Vincent C

          Hi Andrew,

           

          Capture d’écran 2017-07-31 à 14.47.19.png

           

          Create a parameter for urser's Month choice.

          Then use a calculated field T|F as a filter:

           

          IF DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice]) AND DATEDIFF('month', [Order Date], [monthChoice]) = 0

          OR  DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice]) AND DATEDIFF('month', [Order Date], [monthChoice]) = 1

          OR DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice])-1 AND DATEPART('month', [Order Date]) = DATEPART('month', [monthChoice]) THEN TRUE

          ELSE FALSE END

           

          And select TRUE.

           

          You can use this kinda same field to color each month value:

           

          IF DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice]) AND DATEDIFF('month', [Order Date], [monthChoice]) = 0

          THEN 'Current Month' ELSEIF DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice]) AND DATEDIFF('month', [Order Date], [monthChoice]) = 1

          THEN 'Previous Month' ELSEIF DATEPART('year', [Order Date]) = DATEPART('year', [monthChoice])-1 AND DATEPART('month', [Order Date]) = DATEPART('month', [monthChoice]) THEN 'Same Month Previous Year'

          ELSE 'NOTOK' END

           

          By placing it on the color mark.

           

          As I'm using 10.3, I can't share you a workbook, sorry.

          1 of 1 people found this helpful
          • 2. Re: Previous month and year data
            mahesh p

            THIS IS FOR CURRENT MONTH

            MONTH(TODAY())=MONTH([Order Date]) AND YEAR(TODAY())=YEAR([Order Date])

             

            previous MONTH

            MONTH([DATE])=MONTH([Order Date]) AND YEAR([DATE])=YEAR([Order Date])

             

            am working on remaining

            • 3. Re: Previous month and year data
              Zhouyi Zhang

              Hi, Andrew

               

              You can create a calculation field for these 3 period as shown below

               

               

              Hope this could help

               

              ZZ

              1 of 1 people found this helpful
              • 4. Re: Previous month and year data
                Andrew Morgan

                Thanks a lot ZZ. You are amazing.

                 

                Just one thing now I want to calculate % change wrto current month. I want % change for month and % change for previous year same month.

                 

                Is that possible and how?

                • 5. Re: Previous month and year data
                  Zhouyi Zhang

                  Hi, Andrew

                   

                  I take Current Month vs Previous as example below

                   

                   

                  You can use similar calculation to Current Month vs same month last year.

                  Hope this could help

                   

                   

                  ZZ

                  1 of 1 people found this helpful
                  • 6. Re: Previous month and year data
                    Andrew Morgan

                    Thank ZZ.

                     

                    You are a champ.