3 Replies Latest reply on Jun 28, 2016 11:45 AM by Mikhail Christiansen

    How can I allow users to toggle between fiscal year and calendar year using a parameter?


      My data table contains two columns--one for invoiced date (year and month) and one for invoiced amount.

      I'd like for users to be able to toggle between Fiscal Year view and Calendar Year view via parameter.


      I copied my date field and set the fiscal year start date for the copied field to October.  Now I have one date field which will display invoiced amount based on calendar year and another based on fiscal year start.


      I created a "date to display" calculated field which is equal to either the calendar year or fiscal year date field depending on the parameter chosen.  However, "date to display" did not pick up the fiscal year start property as I hoped it would.


      Is there a better way to switch between fiscal year and calendar year views either by specifying the fiscal start month via parameter or by duplicating my or by using additional date fields?


      I've attached a simplified workbook in order to show the steps I've taken so far.  The dashboard shows totals by calendar year, fiscal year, and the calculated "date to display" year.

        • 1. Re: How can I allow users to toggle between fiscal year and calendar year using a parameter?
          Mark Holtz

          Hi Jack,


          You appear to be headed down the path I'd recommend, but in your underlying data, you don't actually have anything differentiating fiscal dates and calendar dates.  Essentially with fiscal dates, you want to have 2 date columns for every transaction--one calendar/actual date, and then a fiscal period (usually year, but could get down to fiscal months).


          So in your explanation with October being the split, 9/28/2013 would fall into FY 2013 and 10/2/2013 would fall into FY 2014.  If the relation from calendar to fiscal is static (i.e., bump anything from October 2013 to 1/1/14).


          Further, to take it to fiscal months, you would "bump" up any date up to the Nth of the month backwards (i.e., 6/5/13 -- 7/4/13 = Fiscal Month 6/1/13), then you can achieve your "different dates" via a calculated field. If, however, each month/year has a different cutoff, then you'll have to hard-code the breaks in a calculated field and maintain them. (e.g., 6/5/13 -- 7/4/13 = 6/1/13 and 7/5/13 -- 8/3/13 = 7/1/13 and 8/4/13 -- 9/5/13 = 9/1/13)


          Back to your example, assuming the October cutoff is consistent every year, we can just "bump" every date that is between October 1 and December 31 up to the next year. (1/1/nextyear).


          Then, at a year level, you can access the Fiscal Year you want.


          Attached is your workbook. Let me know if that's not what you were after.

          • 2. Re: How can I allow users to toggle between fiscal year and calendar year using a parameter?
            Willem Botha

            Hi Jack


            Another way to do it is to create the same worksheet twice, substituting Calendar Date for Fiscal Date in the one, then let the user toggle between sheets in a dashboard, rather than toggling between dates.




            1 of 1 people found this helpful
            • 3. Re: How can I allow users to toggle between fiscal year and calendar year using a parameter?
              Mikhail Christiansen

              Hi Jack,


              You probably are long past this issue, but I wanted to include another option for people who might come here looking for an answer. The above-stated answer by Mark Holtz only works as far as the years are concerned, but leaves you with all of your new Fiscal Year dates as 1/1/NewYr. So, the month of FY all will be January 1st dates. For my problem, I needed to keep all the months for a YTD calculation, but still wanted to add in the FY toggle for the user.



              In order to get a viable solution, I used the DATEADD function. This is the formula I used for Fiscal Year instead of what was used in your old workbook. Fiscal Year = DATEADD('month',3,[Date from Source]). Then, I formatted the month of Date to Display as Numeric.




              Afterwards, the "Month of Date to Display" column gives you the accurate Period of the Fiscal Year. October = "01", January = "04", etc.



              I have attached the updated workbook with the new solution on the second worksheet. If you have any questions, feel free to reach out.




              1 of 1 people found this helpful