Ok but you realize you would only be looking at 11 months of data if you do it that way. Your intital post was asking for the last 12 months of data from an user entered parameter. including 5-1-09 is only 1 day, not a full month.
The formula is in two parts to get your begin and end dates
part 2 (end date) uses the parameter field, extracts the day part (1-31) and subtracts that from the parameter date to give you the last day of the previous month of the parameter. I purposefully did not make it go to the first day of the current month because technically it should not be included in a 12 month analysis. If you want it to to be the first day of the month just add 1 to the day subtraction portion.
dateadd("d",-(datepart("d",{?My Parameter}))+1,{?My Parameter})
The other is a little trickier and I really don't think it is what you want... to go back 11 months instead of 1 year change it from subtracting 1 year to 11 months.
Dateadd("m",-11,(dateadd("d",-(datepart("d",{?My Parameter}))+1,{?My Parameter})))
This formula does a double date add. The inside part converts the parameter to the first day of the month and year of the parameter entered. This is the same code as the first part of your request.
The outside dateadd converts that date to 11 months prior (or 1 year prior in the original post)
Edited by DBlank - 11 May 2009 at 12:58pm
IP Logged
