1 Reply Latest reply on Nov 3, 2013 5:51 AM by kevin.lynch

    How to reflect YTD budget accurately?

    miki.hayashi

      Hi,

       

      I have monthly budget data for all departments and all account types for the entire fiscal year.  Our fiscal year starts on July.  My data contains 1 fiscal year worth data. 

       

      I am having problem querying correct YTD fiscal year budget figures using calculated field.  I used the following formula:

       

      IF

          DATEDIFF('year',[Effective Date],[Reference Date2] )=0

      AND

          MONTH([Effective Date] )<= MONTH([Reference Date2] )

      THEN

         [Budget]

      END

       

       

      I made Reference Date2 parameter to show ranges of dates from July 1, 2013 to June 30, 2014 with 1 month increment.

       

      I was able to show correct YTD figures from July 2013 to December 2013.  Although I can only select 1 month (instead of multiple months), from July 2013 to December 2013, each month selected picked up YTD budget. 

       

      When I selected January 2014, YTD budget value picks up the sum of January 2014 budget only and drops July 2013 to Dec 2013 budget.

      When I select June 2014, YTD budget is the sum of January 2014 budget through June 2014 budget, omitting July 2013 through December 2013 budget.  When I add YTD budget as of June 2014 and that of December 2013, that figure is the correct annual budget.

       

      How can I get correct YTD budget figure when I select January 2014 onwards? 

      Do I need to create a table to define our fiscal year first?

       

      Any thought and advice is very much appreciated.

       

      Miki