Year To Date Sales Compared to Last Year To Date

[jakebailey]

Hi - I tried searching the forum for a similar post but couldn't find one. I have a couple of things I need help or direction with.

See attached example workbook.

1. I have a workbook with invoice line data that spans multiple years. I need to calculate a running total of YTD (Year To Date) sales and another running total of LYTD (Last YTD) sales by customer. At the top of the attached report I have a cell to enter a date that I need the calculations to be based on. For example if I enter 3/15/2011 then I need total sales for this Fiscal Year up until 3/15/2011 and total sales for last fiscal year up until the same day and month. If I change the date then the calculations need to change with it.

2. To complicate things further my fiscal year starts 7/1 and ends on 6/30.

I added another column called "Calendar Year" but I'm not sure if that really helps me. Instead I think I should add a column called "Period" but then again I'm not 100% sure if that will help me either. The fiscal calendar is kind of throwing me for a loop.

I appreciate any and all help or direction on what solution would be best. I'm ok with VBA, Pivot Tables, etc;

[JimmyTheHand]

See the attached workbook for one possible solution.

The final formula is in (C5 : D5), and can be extended downwards.
Cells (C2 : D3) contain start and end dates of the current fiscal period. These are helper cells, and I'm sure they could be incorporated into the final formula, but it would be much more difficult to understand that way.
The EDATE() function used to calculate these dates is included in Analysis Tool Pack.
Also, please note that the formula works with the first 5000 rows of data only.
It can be extended to any fixed number of rows or, using named ranges, to an undefined number of rows as well.

Jimmy

[jakebailey]

Thanks for the reply Jimmy. I installed Analysis Toolpack and then opened your workbook but I'm getting #Value! error in cells C22 and C55.
If I change the periods in cell D2 to forward slashes then I at least get a date of 7/1/2009. For example the original formula has

=DATEVALUE(YEAR(EDATE(D1,6))-2 & ".07.01")

but if I change it to =DATEVALUE(YEAR(EDATE(D1,6))-2 & "/07/01") then I get 7/1/2009.

Is that what I'm supposed to get? Thanks again for your help.

-Jake

[mancubus]

=EDATE(D1,6) produces 9/15/2011 (adds 6 months to date in D1.)

=YEAR(EDATE(D1,6)) is the year part of 9/15/2011, which is 2011. Added bit "-1" ("-2") makes it 2010 (2009).

DATEVALUE function converts text format date to number format date.

so if desired result is 7/1/2010 or 7/1/2009 then that's OK.

[JimmyTheHand]

If I change the periods in cell D2 to forward slashes then I at least get a date of 7/1/2009.

That's fine. My Windows & Excel use periods as separators between year, month and day, while in other languages it's different. It usually doesn't matter in formulas, because Excel stores date values as floating point decimal numbers, and displays them properly, whatever your language version is. That's why I forgot to take into account that here we have a conversion from string to date, and English Excel won't like converting periods. Sorry.
And yes, you need to replace the periods by slashes.

Is that what I'm supposed to get?

I don't know
You see, I'm always having problem with foreign date formats, more precisely, the order of values, because different languages use different order. Take 7/1/2009, for example. Is it

• 7th of January,
• or 1st of July?

This latter is the desired value. The purpose of (C2 : C3) is to present the start and end of the fiscal period we are calculating with.
C2 is the beginning of the fiscal year, which is always the 1st of July that precedes the actual date (if I understood correctly). This is what you're supposed to get.
D2 is exactly C2 minus 1 year.
C3 is the date the calculations are based on.
D3 is exactly C3 minus 1 year.

Hope it is clear now.

Jimmy