3 Replies Latest reply on Apr 3, 2018 6:01 AM by Eric Hammond

    Year to Date vs prior year with custom Month

    Rory Gibson

      Hi All

       

      I am trying to create a graph which displays current year vs prior year, but where people can select the month they want to view up to.

       

      The problem is, in the way I have set it up, the only way I can achieve this is to have a list whereby they have to select all relevant months.

       

      I would like it whereby they can simply select from a dropdown list 'January' (for example), and it will sum all revenue for this financial year up until the end of January, and last financial year up until January.

       

      Is this possible? Please see graph below:

       

       

      Thanks

       

      Rory

        • 1. Re: Year to Date vs prior year with custom Month
          Eric Hammond

          Hi Rory,

           

          Calculated fields don't take into consideration the start of the fiscal year, so that needs to be handled as part of the calculations.  Let's assume a fiscal year start of August for the example below, so calculations must be offset by five months to account for that.  Create a couple of calculated fields for later reference:

          • [Fiscal Year] = YEAR(DATEADD('month',5,[Order Date]))
          • [Fiscal Month] = MONTH(DATEADD('month',5,[Order Date]))

           

          The dropdown list of months will come from a parameter.  Right-click anywhere on the data pane and click Create, Parameter.  Date type = String, Allowable values = list.  The values will be 1 through 12 (months), and the "display as" will be set to month names.  Once that has been created, right-click on the parameter (in the parameters section at the bottom of the data pane) and click "show parameter".

           

          Next, reference this parameter in a calculated field that will be used to filter.  The version below shows the two most recent fiscal years that include the selected month.  Picking October when we are in June will compare the previous fiscal year through October to the year before that.  Picking October when we are in October will compare the current fiscal year to the previous fiscal year through October. Making the details work on this proved to be a challenge; simplify or build on it according to your need.

           

          IF [Fiscal Year] >= YEAR(TODAY()) - 1

                   - IF MONTH(TODAY()) < INT([Month YTD Parameter]) THEN 1 ELSE 0 END

                   + IF INT([Month YTD Parameter]) >= 8 THEN 1 ELSE 0 END

              AND [Fiscal Year] <= YEAR(TODAY())

                   - IF MONTH(TODAY()) < INT([Month YTD Parameter]) THEN 1 ELSE 0 END

                   + IF INT([Month YTD Parameter]) >= 8 THEN 1 ELSE 0 END

              AND [Fiscal Month] <= IIF(INT([Month YTD Parameter]) < 8

                                          ,INT([Month YTD Parameter]) + 5

                                          ,(INT([Month YTD Parameter]) + 5) % 12)

          THEN "In"

          ELSE "Out"

          END

           

          • 2. Re: Year to Date vs prior year with custom Month
            Rory Gibson

            Hi Eric.

             

            This is great, worked perfectly! Thanks so much for your help.

            • 3. Re: Year to Date vs prior year with custom Month
              Eric Hammond

              I am glad that it helped, Rory!  Will you please mark the response as correct?