1 Reply Latest reply on Dec 9, 2016 9:03 PM by Shinichiro Murakami

    How Do I Create Date Range For Columns Shelf

    Jonathan Mattox

      Hello and thanks for viewing my question,

       

      I am trying to calculate budgeted money obligations over years. Certain funds are allocated over a certain number of years. There are several different funds (funds = states in the attached workbook) and each fund has to spend a predefined % of its budget over the predefined number of years that fund has to be spent. The only date I have is the year the money was issued, so I can do calculations for the first year and show it just fine, but I need a way to add the years between the year the money was issued and the current year we're in now (calculating the percentage of money that should be spent for each bar on the row. For example, let's say that:

       

      Alabama has a Fiscal Year issued date of 2014 and has 3 years to be spent (1st year has to have 50% spent, 2nd year needs 75% spent, 3rd year needs 100% spent)

      Arizona has a Fiscal Year issued date of 2011 and has 5 years to be spent (1st year has to have 15% spent, 2nd year needs 35% spent, 3rd year needs 65% spent, 4th year needs 90%, 5th year needs 100%)

      Arkansas has a Fiscal Year issued date of 2015 and has 2 years to be spent (1st year has to have 90% spent, 2nd year needs 100% spent)

       

      Ideally I would like to show this in a bar chart and need the data to look like this on the rows (with the bar for each year being the amount that should be spent each year of their budget:

                     |

      _______ |__________________________

      Alabama | 2014 | 2015 | 2016 (current year)

      Arizona   | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 (current year)

      Arkansas| 2015 | 2016 (current year)

       

       

      The conversion of the attached workbook vs what I'm looking for:

       

      YEAR(Order Date) = Year money was issued

      State = Fund

      SUM(Sales) = Budget

       

      I've seen several articles showing about creating a date range from a parameter, but I do not think that will work for our situation. I feel confident a calculated field will be involved I'm just not sure how to accomplish this. I would appreciate anyone's help to come up with a solution. Please see attached workbook to show the kind of layout I'm looking for: