10 Replies Latest reply on Jun 28, 2017 11:22 AM by Shruti Chawla

    Current YTD vs Previous YTD

    Mikey Michaels

      Hello All,

      I hope this message finds you well.

      On the attached, I have revenue for 12 periods in 2016 and 5 periods in 2017. I would like to create a calculation that would compare revenue for the first 5 periods in 2017 vs the first 5 periods in 2016. I would like the calculation to show the $ increase/decrease and the percentage increase/decrease. Also, this calculation needs to be dynamic so when new periods are added in 2017, the calculation would change and add this period.

       

      Many thanks for your time,

      Mikey

       

        • 1. Re: Current YTD vs Previous YTD
          Stephen Shepperd

          This is such a good question, Mikey.

           

          First, you'll need to create an equation that separates the Fiscal Periods by the last three digits. This will allow us to tell Tableau what to refer to when we are doing our math. It will look like this:

           

          Next, drag that to the right of the [Fiscal Year Periods] field on the Rows shelf. Then, right-click the pill and uncheck "Show Header".

           

          Next, we'll create a Table Calculation to create the YoY $ Difference value. On the [Revenue] field that is already out there, right click it, hover over "Table Calculation" and select "Difference". Then, right click that same pill, click "Edit Table Calculation", and then make the Table Calc screen look like this:

           

          This will give you your YoY $ Difference value.

           

          Do the same steps above to create the YoY% Difference but instead of selecting "Difference", select "Percent Difference". The same steps should follow.

           

          After that, you should be able to add Revenue back to the view and have all three metrics displaying like this:

          Hope this helped!

          1 of 1 people found this helpful
          • 2. Re: Current YTD vs Previous YTD
            msa s

            do we need to drag revenue field twice on the row shelf ?

            • 3. Re: Current YTD vs Previous YTD
              Stephen Shepperd

              This is what the set up looks like:

              • 4. Re: Current YTD vs Previous YTD
                Keith Troutt

                Here's a slightly different approach. Requires a few more calculations but allows you do some different things with your views.  There are 4 main calculations:

                 

                1. Date Conversion which makes a date out of your Fiscal Year Period

                Date(Right([Fiscal Year Periods],2)+'/1/'+left([Fiscal Year Periods],4))

                 

                2. Last Period Calculation called Last Period:

                {MAX([Date Conversion])}

                 

                3. FYTD Flag

                DATEDIFF('year',[Date Conversion],[Last Period])=0

                 

                4.  PY Flag

                DATEDIFF('year',[Date Conversion],[Last Period])=1
                AND
                DATEPART('month',[Date Conversion])<=DATEPART('month',[Last Period])

                 

                 

                From there, we can build FYTD Revenue and Prior Year Revenue metrics which can be used in a summary.  See the attached for details. 

                 

                Thanks!

                1 of 1 people found this helpful
                • 5. Re: Current YTD vs Previous YTD
                  Mikey Michaels

                  Keith,

                  This is EXACTLY what I'm looking for...thank you so much for all your help!

                  I do have one remaining problem -> just yesterday my IT department changed my date format in my dataset. I now have a DATE field called "Revenue Date", which is nice since I will not have to compute a date conversion anymore; however, this has caused issues when I tried ti implement your solution. On the attached, I try to mirror your formulas; however, the new date field is causing me some issues. On the attached, instead of comparing the entire current year to date vs the previous year to date, for some reason my visual is only comparing Jan and Feb 2017 vs Jan and Feb 2016. Can you take another look?

                  Again, thank you so much for all your help!

                   

                  Cheers,

                  Mikey

                  • 6. Re: Current YTD vs Previous YTD
                    Keith Troutt

                    Hi Mikey,

                     

                    In your original question, you were looking at fiscal periods. When does your fiscal calendar begin? For fiscal year to date calculations, I typically take the revenue date, create an adjusted revenue date that moves the date forward so that the fiscal start month = January.

                     

                    For example, my company fiscal calendar begins in May. So my formula would look like:

                    Adjusted Revenue Date: dateadd('month',8,[Revenue Date])

                     

                    I would also do the same thing for the Last Period

                    adjusted last date: dateadd('month',8,[Revenue Date])

                     

                    Then all the work you have done so far would then point to these date calculations for the FYTD flag and the PY Flag.

                     

                    I've added a parameter to your workbook so you can see how this could all work. The final step, you will want to set the date properties on your 'Revenue Date' for the fiscal start month.  Two places that I know of that you need to do this.

                    1. Right click Revenue Date and choose Default Properies -> Fiscal Year Start -> Choose the appropriate month

                     

                     

                     

                    2. Right click the data source icon and choose Date Properties. Select the appropriate fiscal starting month.

                     

                     

                    Attached is your workbook which you can look how I formatted your Revenue Date for a fiscal start month of October.

                    • 7. Re: Current YTD vs Previous YTD
                      Mikey Michaels

                      thank you so much, Keith for your detailed explanation and time!

                      • 8. Re: Current YTD vs Previous YTD
                        Shruti Chawla

                        Very Informative Keith! Thank you.

                        Going one step further, how would you do this for just the current month vs same month previous year? Could you do a toggle between the two i.e. Toggle between YTD comparison and Month comparison.

                        • 9. Re: Current YTD vs Previous YTD
                          Keith Troutt

                          HI Shruti,

                           

                          The process is similar with the date flags I described earlier in the thread. You need to create a series of date filters, I always call them flags as I want them to be either True or False.

                           

                          Current Month Flag:

                          Datediff('month',{max(Calendar Date)}) = 0

                           

                          Same Month Last Year Flag:

                          DATEDIFF('month',[Calendar Date], {max(Calendar Date)})=12

                          and

                          DATEPART('day',[Calendar Date])<=DATEPART('day' , {max(Calendar Date)}) ***

                           

                          *** The And portion of the formula is optional. It is a way to ensure you are not comparing a partial month to a full month.

                           

                          To keep the two periods together, I create a third flag.

                           

                          Month over Month Flag:

                          [Current Month Flag] or [Same Month Last Year Flag]

                           

                          To swap select the period you want to display, one method might be to create 2 choice parameter for FYTD and Month over Month. What makes it all work is a calculated field that would switch between the two period filters. In my attached example, the field is called Period Filter

                           

                          Period Filter:

                          If [Period Selection] = 1 THEN [Fiscal YOY Flag] ELSE [Month over Month] END

                           

                          I hope this helps!

                           

                          Keith

                          1 of 1 people found this helpful
                          • 10. Re: Current YTD vs Previous YTD
                            Shruti Chawla

                            Brilliant! Thanks Keith