5 Replies Latest reply on Oct 15, 2019 2:26 PM by Bryce Larsen

    Fiscal YTD Excluding Current Month

    Sebastian Mortillaro

      Hello Tableau Friends,

       

      I am looking to create a date filter to exclude the current month across all years - a fiscal month to date filter of sorts.

      My fiscal year runs from November 1st to October 31st.

       

      For example, it is currently October 11th meaning I want to exclude the month of October across ALL years to allow for YTD comparability:

      See how sales look so low for October since it is only a partial months worth of sales data?

       

      Thanks all!

        • 1. Re: Fiscal YTD Excluding Current Month
          Joe Oppelt

          You need to upload a packaged workbook -- which has a twbX extension rather than just a twb extension.

           

          Do  FILE --> "Export Packaged Workbook"

           

          But you can put a check in your calc to exclude the current month.  Without your workbook I can't give you exact syntax, but it would look something like this:

           

          IF MONTH( today() ) <> MONTH([your date field]) then [measure you are displaying] END

           

          Use that calc instead of your actual measure.  Current month (for all years) will be null.

           

          (You might have to filter out for null on your sheet.)


          If you can apply that concept as I described it, then no need to redo your workbook to upload here.  But if you need more help, upload a TWBX.

          • 2. Re: Fiscal YTD Excluding Current Month
            Michael Hesser

            Perhaps another way to write what Joe has beautifully detailed above might be:

             

            Display Flag

            MONTH([Order Date])<>MONTH(TODAY())

             

            Just drop this into filters and choose to display only TRUE values.

             

            Your calendar will exclude the current month for all years. For example, I displayed JAN-DEC, and it leapt over OCT (leaving only 11 months). This might be confusing for viewers.

             

            But by selecting Show Missing Values on my date field, I returned to my 12-month year.  This also left an awkward blank gap: I'm not sure if this will be an issue for you.

             

            The difference between what Joe has written and this is pretty slight, I think. But one benefit a boolean has is if you have multiple charts with different measures requiring the same fix. Suppose you have one chart of Sales and another of Profit: you can drop your single Display Flag calc into both without needing to write a calculation for each measure.

             

            Happy Vizzing!

            • 3. Re: Fiscal YTD Excluding Current Month
              Bryce Larsen

              Hi Sebastian,

              You can change the Fiscal Year using this method: Tax Dates - Tableau

              For a specific field in your data, you can try right clicking on the field like so:

              Then drag the fields you desire on the columns and rows (eg. MONTH(Order Date) and SUM(Sales)), and YEAR(Order Date) onto the color pane.

               

              To filter out FYTD:

              MONTH([Order Date]) + IF MONTH([Order Date])>=11 THEN -10 ELSE  2 END
              < MONTH(TODAY()) + IF MONTH(TODAY())>=11 THEN -10 ELSE  2 END
              

               

               

              You can use logic from Jim to always show all months if you want:

              IF [FYTD] THEN [Sales] END

              and Hide Nulls Indicator.

               

              Hope this helps!

               

              Best,

              Bryce

              • 4. Re: Fiscal YTD Excluding Current Month
                Sebastian Mortillaro

                Thanks Bryce! Your approach is EXACTLY what I after. Really appreciate all your help

                • 5. Re: Fiscal YTD Excluding Current Month
                  Bryce Larsen

                  Excellent! Glad the Community was able to help.