8 Replies Latest reply on Mar 17, 2017 11:17 AM by Jose Basualdo

    Custom Fiscal Year

    Jose Basualdo


      I need to create my Fiscal Calendar, with these characteristics.

      First weekday: Monday

      First month week: The week with 1st of the month.

      First Fiscal Month: November.

      First Fiscal month week: Week with November 1st.

      Fiscal Quarter: Every 3 Fiscal Months.

      I am attaching an Excel file like example.

      I need to create my Fiscal Date, then I need to separate year, month and day.


      I really need your help.


      Thank you.

        • 1. Re: Custom Fiscal Year
          Lisa Li

          Hey Jose,


          What data are you building off of? If you have a date field, you can right click it and change it's default properties > Fiscal Year Start, and set it as November. then a hierarchy will automatically be created for your criteria.


          If you do not have a date field in the data you're building off of, you'll need to create logic to bin the dates into.



          CoEnterprise | Home

          1 of 1 people found this helpful
          • 2. Re: Custom Fiscal Year
            Jose Basualdo

            Hi Lisa,


            Thanks for your answer.

            Yes, I have a date field, the problem is that my fiscal year begins the week that contains the 1st of November, this means that sometimes it starts before November 1st.

            For example, 2017 began on October 31.

            • 3. Re: Custom Fiscal Year
              Kaz Shakir


              I think what I would suggest is that you create a table that contains the start date and end date for each fiscal year.  Something like this (I created it in Excel):

              And the formulas I used in the various columns are as follows (in row 2)

              StartDate (cell B2): =IF(WEEKDAY(DATE(A2-1,11,1),2)=1,DATE(A2-1,11,1),DATE(A2-1,11,1)-WEEKDAY(DATE(A2-1,11,1),2)+1)


              EndDate (cell C2): =B3-1


              NumberOfDaysInFiscalYear (cell D2): =C2-B2+1


              This is a very odd way of creating Fiscal Years (where it starts on a different date each year), and it creates a very interesting issue, that you can see in the table above: most of your Fiscal Years have 364 days, and every 6th Fiscal Year has 371 days.  Odd, but not a problem, as long as you keep track of it.


              Then you also need to keep track of how many days each month is supposed to have.  In the excel file you attached, most of your fiscal months have 28 days, and a few have 35 days.  And it seems that in those 6th-years, where your Fiscal Year has 371 days, the month of November has 35 days, and in all other years it has 28 days.  And it seems that all other months have the same number of days regardless of which year it is.  This is almost true, except there seems to be some issue in 2000 where 7 days seem to have shifted from January to December - and I don't know why.  If there is some additional rule that can cause this sort of a shift, then you will probably have to expand the above table to have a column for each month that contains the number of days that month is supposed to contain.  Using that information, you can figure out the starting date for each month.


              So, I think if you build out that table, then you should have the information you need in order to be able to determine what fiscal period you are in.


              Hope that helps.


              • 4. Re: Custom Fiscal Year
                Jose Basualdo

                Hi Kaz,


                Thank you.

                It is very interesting your option.

                How do you build the second table?

                I think that we will have the same problem as 2000 every 20 years.


                I really appreciate your time.

                • 5. Re: Custom Fiscal Year
                  Kaz Shakir


                  Regarding how I built the second table, I am attaching your original excel file, with this table included.  You can take a look at the formulas I used to see how it's built.  I also used conditional formatting for the yellow highlights.


                  As I thought about this problem some more, I think your best solution might be to simply use the table you have already built (in your previous attachment), and just carry it out for how many years you need to - such that there is a row for every day, and the columns indicate which fiscal year, fiscal month, and fiscal week, each date corresponds with.  Then you can simply join this calendar table with your transactions table on the date field, and all of your transactions will have the appropriate fiscal year, fiscal month, and fiscal week tied to them.



                  • 6. Re: Custom Fiscal Year
                    Jose Basualdo

                    Thank you for the file.

                    I think that it is the best option to create my own table and join it.

                    The only issue is that I cannot use this option:

                    Thanks for everything.

                    • 7. Re: Custom Fiscal Year
                      Kaz Shakir


                      Actually you can use that option - if you create your own Hierarchy.  I'm attaching a packaged workbook to show you how this might work.


                      For example, if you have a transactions table that looks like this:


                      And your calendar table looks like this:


                      First, join your transactions table with the calendar table, like this:

                      Then your dimensions and measures should look something like this:

                      Convert each of the dimensions to be Discrete rather than continuous (Fiscal Year, Fiscal Quarter, Fiscal Month, Fiscal Week).

                      Next, right click on Fiscal Year, and select Hierarchy -> Create Hierarchy


                      Give the new hierarchy a name, like Fiscal Period.  And, then, repeat the step for Fiscal Quarter, Fiscal Month2, and Fiscal Week.  And you should have something that looks like this:


                      Then, you can drag the [Fiscal Period] pill to the Rows shelf as a dimension in your viz, and it will look like this - note that on the Rows shelf it does not show the name of the Hierarchy, but, instead it shows the name of the highest level of the hierarcy, in this case, that's Fiscal Year:


                      Let me know if this is what you had in mind.


                      • 8. Re: Custom Fiscal Year
                        Jose Basualdo

                        Thank you.

                        I think that I will use this option, but I will continue to thinking how I can do the other option.