2 Replies Latest reply on Jan 11, 2012 8:08 AM by marcramaer0

# Formula help needed: [Last Year Sales] + [Current Year Growth Target]

Hello,

Hope someone can help me. I need help with a custom calculation that computes a total of prior year sales [Sales] and adds a target sales growth for current year [Target Growth]. It will need to follow the display on month, quarter etc. Our fiscal year runs June-May, so the calculations should be limited to June 2011 onwards (Fiscal Year 2012).

If anyone can help it would be much appreciated.

Marc

• ###### 1. Re: Formula help needed: [Last Year Sales] + [Current Year Growth Target]

Which part are you struggling with exactly? From what's written it sounds like the 'Last Year Sales'. If this is the case you could create a calculated field along the lines of SUM(IIF([SalesDate] >= 1 June 2010 AND [SalesDate]<1 June 2011,[Sales],0)).

Without seeing your data I don't know the names of your date field but this formula would sum all of the values in the [Sales] field where the sale happened between June and May - your financial year.

Once the calc field is created you could then add the [Current Year Growth Target] to this in a separate calc field.

• ###### 2. Re: Formula help needed: [Last Year Sales] + [Current Year Growth Target]

Still Stuck on this one, so any help is appreciated. I have figured out how to calculate prior year sales:

IF [Date]<DATEADD('month',-12,#June 1, 2011#) THEN Null

ELSEIF [Date] > DATEADD('month',-12,TODAY()) THEN Null

ELSE [Sales]

END

Now I need to take that calculation and add it to this year's growth targets to get an absolute target value. When I sum the parts together, it totals correctly but I need the calculated field dates to display in the current fiscal year.

I basically need to time shift last years sales into this year and then add the growth on top, so that for instance January 2012 Target Sales = Jan 2011 Sales + Growth Target. All I get is last year sales still displayed in last year's cell - so it needs to somehow be offset.

Hope I am making sense ....

Marc

Any ideas?