3 Replies Latest reply on Sep 14, 2018 6:21 PM by swaroop.gantela

    Aligning Tableau Dates to Corporate Fiscal Dates

    brian.oilar

      Our corporation uses an odd fiscal calendar and I am struggling to figure out how to get Tableau fiscal year dates to align with our calendar.   The issue is that our calendar years start in April, but with the requirement that the first week of the fiscal month begins on a Saturday.  Therefore, most of our fiscal years do not start on April 1st.  Fiscal months rarely start on the 1st of the month as well.   Here is an example on fiscal year start dates:

       

      2015-Q1 Start Date = March 29, 2014

      2016-Q1 Start Date = April 4, 2015

      2017-Q1 Start Date = April 2, 2016

       

      More details are in the attached workbook. 

       

      I've tried setting the date properties to week beginning Saturday and the fiscal year beginning in April, but that didn't even come close.  I also scoured the internet for various techniques. The closest that seems to be a potential method uses the MAKEDATE function, which requires an extract.  (Not possible for what we're doing).  Link.   

       

      Outside of that...I am at a complete loss on how to solve for this.   Any advice would be appreciated!!

       

      Thank you

        • 1. Re: Aligning Tableau Dates to Corporate Fiscal Dates
          suman kumar

          Hi,

           

          Right click on data source like below image and click on Date Properties. Please see below image for reference.

           

          Screen Shot 2018-09-14 at 10.40.55 PM.png

          Then you'll get this option.

          Screen Shot 2018-09-14 at 10.41.06 PM.png

           

          Please mark correct and Helpful if it works for you

           

          Best

          Suman

          • 2. Re: Aligning Tableau Dates to Corporate Fiscal Dates
            brian.oilar

            Thanks for the quick reply.  I did try that already.  As a matter of fact, if you look at the workbook, it shows that I've done that (or should show it)...same results for some reason.  

            • 3. Re: Aligning Tableau Dates to Corporate Fiscal Dates
              swaroop.gantela

              Brian,

               

              I don't think I quite caught the gist, but I just wanted to toss out a possible workaround to MAKEDATE.

               

              I followed the same directions as provided in the link for NewYear and NewMonths.

              Instead of MAKEDATE, I used DATEADD, which relied on using the date literal #2000-01-01#.

              Does your datasource type allow for data literals?

               

              Then New Fiscal Date becomes:

              DATE(DATEADD('day',DAY([Date])-1,

                  DATEADD('month',[NewMonths]-1,

                        DATEADD('year',[NewYear]-2000,#2000-01-01#))))

               

              I think this works as described in the Interworks page, with the first Saturday in April being the

              beginning of the Fiscal Year.

               

              It was not clear to me in your original post why March 29, 2014 was the start of FY 2015-Q1.

              If the condition is to find the Saturday of the week that includes April 1st,

              then it would appear using that criterion, the start of FY2016-Q1 should be March 28, 2015,

              since it is the Saturday immediately preceding April 1st which is a Wednesday.

              I explored that method in the sheet entitled "week of Apr1".

              That uses different calculations for [FirstWeek],[NewMonths],[NewYear].

               

              Please see workbook v18.1 attached in the Forum Thread.