Query Rolling 12

daknight

New Member
Joined
Jan 5, 2005
Messages
34
Can anyone tell me how to write an expression in my query that will pull a rolling 12 months? Actually, I would like a rolling 12 month to date and I would also like a rolling full month (first month will roll off when current month is complete). Could you show me both ways, PLEASE??
The field I am working with is [CompDate]
Thank you very much for your help!
Darla
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You could try this.

SELECT tblDates.CompDate
FROM tblDates
WHERE (((tblDates.CompDate) Between Date()-365 And Date()));

This will do for a year. Change Date()-365 to Date()-30 for a month.

A better solution would be to take the dates required from text boxes and use those as parameters. This would be much more flexible.
 
Upvote 0
Following on from the post from Tanis, if you want your rolling 12 months to take into account leap years then use this as your criteria :

>Day(Date()) & "/" & Month(Date()) & "/" & (Year(Date())-1) And <=Date()

But if your data is out by one day (i.e. today you are looking at yesterday's data), then use this instead for your rolling 12 months :

>=Day(Date()) & "/" & Month(Date()) & "/" & (Year(Date())-1) And < Date()

I'm not 100% certain what you mean by a "rolling full month". Is this where you have 12 months of complete data and a part of the current month, except for the last day of the month? Also, are you looking at data that is a day behind? For example, on the 1st of the month will you be looking at data up to the last day of the previous month?

Andrew :)
 
Upvote 0
Rolling 12 Month

By a Rolling (full) 12 months, I mean I don't want to include any days of the current month since it is not complete.
This is what I have in my query right now:
Between (Date()) And DateAdd("m",-12,(Date()))
and this gives me from Today back to 12 months ago today. So my records are Feb 18 2004 through Feb 18 2005.
What I NEED is Jan 2004 through Jan 2005 and then on March 1st I want my records to be from Feb 2004 through Feb 2005.
I am relatively new to Access and REALLY new to writing expressions, so I really appreciate your help. Sometimes I just don't understand. Thanks for your patience.
 
Upvote 0
How about this?

Between DateAdd("m",-1,Date()) And DateAdd("m",-13,Date())

EDIT Just noticed you want the full months. I'll have a think about it. Off the top of my head you might look at using the Dateserial function.
 
Upvote 0
For the rolling 12 months bit try this in your criteria :

>DateAdd("d",-Day(Date()),DateAdd("yyyy",-1,Date())) And <=DateAdd("d",-Day(Date()),Date())

During Feb 05 this willl give you the 12 months data from 1 Feb 04 to 31 Jan 05. On the 1st of March this will give you the data from 1 Mar 04 to 28 Feb 05.

Your previous post stated you wanted Feb to Feb so if you actually want 1 Feb 04 to 28 Feb 05 please advise.

HTH, Andrew, :)
 
Upvote 0
Looks like you've got a few options to work on. Following Norie's suggestion, place this in the criteria row for [CompDate]:
Between DateSerial(Year(Date)-1,Month(Date),1) And DateSerial(Year(Date),Month(Date),1)-1

Will give you a 12-month period (full months) ending on the last day of last month

Denis
 
Upvote 0
Rolling 12

Thanks for all the help with this. I used Andrew's expression and it did exactly what I wanted. I'm anxious for Mar 1 to 'roll' around!!
Thanks
Darla
 
Upvote 0
Re: Rolling 12

I have been working on this formula for 2 days now. Someone please help me. I have an access database that has fields for Accounting Date, P/N, P/N Description, Quantity and Cost. My goal is as follows:

Using the last Accounting Date (different query ---> ([qry__Max_Date_Stores]![MaxofAccounting Date]),)go back 13 months and sum quantity and cost in a separate bucket for each month. In the formula I am about to show my Maximum Accounting Date is equal to August 6th. I can get July and August to balance but June is off.

Code:
Qty CP - 2: Sum(IIf([tbl_Stores]![Accounting Date]>=DateSerial(Year([qry__Max_Date_Stores]![MaxofAccounting Date]),Month([qry__Max_Date_Stores]![MaxofAccounting Date])-2,1)+0 And [tbl_Stores]![Accounting Date]<DateSerial(Year([qry__Max_Date_Stores]![MaxofAccounting Date]),Month([qry__Max_Date_Stores]![MaxofAccounting Date])-1,1)-1,[tbl_Stores]![Quantity],0))

Somebody please help
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top