Pull only month to date for previous year

[beth4530]

I am comparing the past FY (July-June) to the current FY. How can I pull in only the matching months for the current fiscal year to date? I don't want to see any months beyond the current month for the past fy.
MED MEDEVAL
FY12 FY13 +/- Seen FY12 FY13 +/- Seen
Jul 656 61% 1066 65% 410 46 90% 108 86% 62
Aug 737 59% 1031 63% 294 36 73% 101 82% 65
Sep 796 61% 1043 65% 247 106 82% 94 82% -12
Oct 847 62% 1200 62% 353 140 79% 112 77% -28
Nov 802 58% 1126 64% 324 89 72% 90 77% 1
Dec 937 61% 978 58% 41 116 81% 106 78% -10
Jan 966 59% 1322 65% 356 95 79% 131 80% 36
Feb 970 62% 1066 63% 96 109 84% 106 75% -3
Mar 1045 64% 0 0% -1045 121 76% 0 0% -121
Apr 986 64% 0 0% -986 97 80% 0 0% -97
May 1144 64% 0 0% -1144 95 85% 0 0% -95
Jun 1051 63% 0 0% -1051 101 80% 0 0% -101
Total Seen 10,937 64% 8,832 65% -2,105 1,151 90% 848 86% -303

 

[IanWaterman]

I assume you FY 12-13 runs for 1/Jul/2012 to 30/Jun/2013 and you want to compare this to prior year 1/Jul/2011 to 30/Jun/2012

Firstly you need to select your date range this can be based on Currentdate or a report paramter. I will use currentdate just replace with appropraite date parameter if that is better for you

Select statement

If Month(currentdate) < 7 then
Yourdatefield < Date(Year(currentdate), 7, 1) and Yourdatefield >= Date(Year(currentdate)-2, 7, 1)
Else
Yourdatefield < Date(Year(currentdate)+1, 7, 1) and Yourdatefield >= Date(Year(currentdate)-2, 7, 1)

Group report by formula
@Month
Month(YOurdatefield)

Create two formula
@CurrentYear
If Month(currentdate) < 7 then
(if Yourdatefield < Date(Year(currentdate), 7, 1) and Yourdatefield >= Date(Year(currentdate)-1, 7, 1)
then yourvaluefield else 0)
Else
(If Yourdatefield < Date(Year(currentdate)+1, 7, 1) and Yourdatefield >= Date(Year(currentdate), 7, 1)
then yourvaluefield else 0)

@PriorYear
If Month(currentdate) < 7 then
(if Yourdatefield < Date(Year(currentdate)-1, 7, 1) and Yourdatefield >= Date(Year(currentdate)-2, 7, 1)
then yourvaluefield else 0)
Else
(If Yourdatefield < Date(Year(currentdate), 7, 1) and Yourdatefield >= Date(Year(currentdate)-1, 7, 1)
then yourvaluefield else 0)

Add sum summaries to these formula to Month group footer, suppress details and group header

Ian

 

[IanWaterman]

Sorry missed the bit about cuurent month PY

Select formula becomes

If Month(currentdate) < 7 then
Yourdatefield >= Date(Year(currentdate)-1, 7, 1)
or (Yourdatefield >= Date(Year(currentdate)-2, 7, 1) and Yourdatefield <= Dateserial(year(currentdate)-1, month(currentdate)+1, 01)-1)
Else
Yourdatefield >= Date(Year(currentdate), 7, 1)
or (Yourdatefield >= Date(Year(currentdate)-1, 7, 1) and Yourdatefield <= Dateserial(year(currentdate)-1, month(currentdate)+1, 01)-1)

Modify the year formulae on the same lines

Ian