Here is a table where we are looking for calculations to find this year's fiscal cumulative to-date and the previous year's fiscal cumulative to date. Once we have those totals, we can make a pivot table on the data including the columns that are referenced in the lsCode (hidden in this table example):
| EndDate | lsCode | Month | Year | ThisYr | LastYr | ThisFiscal | LastFiscal |
|---|---|---|---|---|---|---|---|
| 2/29/2012 | I.A.1FRM-02 | February | 2012 | 6818 | 8010 | 48004 | 62641 |
| 2/29/2012 | I.A.1FRM-03 | February | 2012 | 787 | 1101 | 5047 | 5925 |
Here is one way of finding the fiscal cumulative to-date using SUMIFS (available in Excel 2010 on):
=SUMIFS($F:$F,$A:$A,">="&DATE(YEAR($A2)-1,MONTH($A2)+MOD((6-MONTH($A2)),12),31),$A:$A,"<="&EOMONTH([@EndDate],0),$I:$I,"="&[@lsCode])
$F:$F is the row where the data resides in a 15 row table
$A:$A is the date field, so
$A:$A,">="&DATE(YEAR($A2)-1,MONTH($A2)+MOD((6-MONTH($A2)),12),31)
finds the first day of the current fiscal year, in this case, July 1, 2011
$A:$A,"<="&EOMONTH([@EndDate],0)
finds the last day of the month of the current reporting period. [@EndDate] is the cell (A2 for example) where the specific criteria resides that is being examined. The table this formula came from has been locked in as a table with the Ctrl-T command providing the user with an exact cell reference.
$I:$I,"="&[@lsCode]
finds the unique code identifier for this row and provides the pivot table a way to total only this statistic from July 1 of this fiscal year to the current date.
To find the previous fiscal year, modify the above formula so that it is looking at the previous year (see bold red):
=SUMIFS($F:$F,$A:$A,">="&DATE(YEAR($A2)-2,MONTH($A2)+MOD((6-MONTH($A2)),12),31),$A:$A,"<="&EOMONTH([@EndDate],-12),$I:$I,"="&[@lsCode])
="FY"&IF(MONTH([@Date])<7,YEAR([@Date]),YEAR([@Date])+1)
Where [@Date] is your date column and 7 in the formula is the starting month of your fiscal year.
For Fiscal Quarters:
="Q"&CHOOSE(MONTH([@Date]),3,3,3,4,4,4,1,1,1,2,2,2)
Where [@Date] is your date field and the sets of numbers match what quarter is displayed starting with January. In this example, January through March is the third quarter (3,3,3), April through June the second quarter (4,4,4) and so on.
="Qtr"&MATCH(MONTH(EDATE(A1,6)),{1,4,7,10})&" FY"&YEAR(EDATE(A1,6))
FISCAL YEAR ONLY:
="FY"&YEAR(EDATE(A1,6))
Explanation: Using YEAR() combined with EDATE(), we can determine the fiscal year. If, for instance, the month of the date in cell A1 is July (month 7) 2009, we go month 7 plus 6 months forward from July 2009 to get an EDATE of January 2010. The YEAR() function generates a year only, so
="FY"&YEAR(EDATE(7/1/2009,6))
BECOMES:
FY2010
FISCAL QUARTER ONLY:
="Qtr"&MATCH(MONTH(EDATE(A1,6)),{1,4,7,10})
Explanation: If, for instance, the month of the date in cell A1 is January (1) we will add 6 to it to get an EDATE of 7. The lookup array {1,4,7,10} provides a relative position for the EDATE. The relative position of EDATE 7 is the 3rd position in the array, or, for our purposes, the 3rd Quarter of the fiscal year.
The following formulas are ones I have found useful over the years for calculations in MS Access queries. In the following examples:
[Start Date], [End Date] and [Enter Year] are variables prompting for information from the user.
[date] is an already determined date from a field elsewhere in the table. You would have to use the name you have assigned that column for this to work properly.
