2 Replies Latest reply on Nov 21, 2017 12:07 PM by Wes Reneau

    LOD Calculation - Find Previous Year Ending Value

    Wes Reneau

      Hi Friends,


      I'm trying to write a calculated field that returns the previous fiscal year ending value to create a Net Change calculation. In my scenario June is the last month of the fiscal year. It is the 'starting' point for any growth that happens in July through June in the next FY. I cannot use a quick table YTD Growth calculation because this is used in a sheet that has a month quick filter already applied. I was attempting to create a FIXED LOD, but can't seem to get it to work.


      For example, ending numbers for each month are:

      June FY17 = 100


      July FY18 = 110

      Aug FY18 = 105

      Sep FY18 = 125



      If I wanted to find the FYTD Net Change as of September the calculation would be

      (125-100)/100 = 25% increase


      This is my attempt and it gives me a bogus number. I would expect it to return 100.

      {FIXED [Country Name], [AS_OF_DATE]>=Date('06/1/2017') AND [AS_OF_DATE]<=Date('06/30/2017'):Sum([Total Children])}



      Any ideas on what I'm doing wrong within my calculation?




        • 1. Re: LOD Calculation - Find Previous Year Ending Value
          Joe Oppelt

          Make a [Fiscal Year] dimension.



          IF Month([Date]) >6 then YEAR([Date])+1 ELSE YEAR([Date]) END


          So every row will have a fiscal year value.  Jan-June will have the value of its own year.  But Jul-Dec will be in the next fiscal year.


          Then you can put [Fiscal Year] on the sheet and you can know what the max value for any fiscal year was that way.


          If you need help doing this, a sample workbook would help so that I can show you.

          • 2. Re: LOD Calculation - Find Previous Year Ending Value
            Wes Reneau

            Hey Joe,


            Thanks for the suggestion, it pointed me in the right direction. Here's what I did....


            Created calculated field named 'Fiscal Year - Calc'

            if MONTH([AS_OF_DATE]) > 6 then

            YEAR([AS_OF_DATE]) + 1

            else YEAR([AS_OF_DATE])



            Created 'LOD Prev Fiscal Year Ending Value'

            { FIXED [Fiscal Year - Calc], [Partner Name]:SUM([Audit Actuals])}


            I used the second calculated field within a tooltip so i could use it within the sheets title.


            Works like a charm!