DSum not calculating the "correct" value

loopa

New Member
Joined
Sep 3, 2006
Messages
5
Hi all

I'm battling with DSum to sum the number of sales within a rolling 3 month period (the latest three months).

My formula is as follows...
Code:
RunSum: DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "# AND [MthdtSale]<=#" & [MthdtSale] & "#")
...which I was hoping translated in to sum all "Sales" in "Table1" for when the months in which sales took place are between two months ago and within the latest sales month. So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in both Jan-17 and Feb-17 (there will be sales in later months too, so important to specify a date range).

Even when I use...
Code:
RunSum: DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "#")
It doesn't sum all sales after a given date - if the date was 01/04/2017, it seems to be summing everything from three months ago...not two.

Issues with DSum are quite common it seems (loads of articles online!!), but I haven't managed to amend the query as necessary to get it working as intended.

Please help...it's driving me mad! :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi,
this is not clear:
...which I was hoping translated in to sum all "Sales" in "Table1" for when the months in which sales took place are between two months ago and within the latest sales month. So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in both Jan-17 and Feb-17 (there will be sales in later months too, so important to specify a date range).

You say you want a three month rolling period. But your example only has sales from two months.

Can you give a more exact explanation of what period you want summed in the result, with a new example?
 
Upvote 0
Sorry...thought one thing, partly wrote another! It's for a rolling 3 month period.

My text should have read...
So If MthdtSale was equal to 01/02/2017, I'd expect my DSum to return a value of the sales in Dec-16, Jan-17 and Feb-17.

Thanks.
 
Upvote 0
Okay, so looking at this you have a big problem in the formula, which is that you are not comparing the dates in the table to anything (other than themselves). You need a date to compare to:

Code:
=DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[MthdtSale]) & "#")
Instead expecting something like:
Code:
=DSum("Sales","Table1","[MthdtSale]>=#" & DateAdd("m",-2,[B][COLOR="#FF0000"]Date()[/COLOR][/B]) & "#")

As is, yes you will get all records in the table because yes every date is at least greater than or equal to a date 2 months earlier than itself.
 
Last edited:
Upvote 0
Here's an example but see my previous post for the first (main) problem:

Code:
DSum
(
"[Sales]",
"[Table8]",
"[MthDtSale]>=DateSerial(year(#2/1/2017#),Month(#2/1/2017#)-2, 1) And [MthDtSale]<DateSerial(year(#2/1/2017#),Month(#2/1/2017#)+1,1)"
)

Result is 1022

Sample data used for the test:
---------------------
| Sales | MthDtSale |
---------------------
|     1 | 30-Nov-16 |
|     2 | 01-Dec-16 |
|     4 | 15-Dec-16 |
|     8 | 31-Dec-16 |
|    16 | 01-Jan-17 |
|    32 | 15-Jan-17 |
|    64 | 31-Jan-17 |
|   128 | 01-Feb-17 |
|   256 | 15-Feb-17 |
|   512 | 28-Feb-17 |
|  1024 | 01-Mar-17 |
|  2048 | 15-Mar-17 |
|  4096 | 31-Mar-17 |
|  8192 | 01-Apr-17 |
---------------------
 
Upvote 0
Thanks for your response.

However, since I want to calculate the rolling 3 months sum for every record in the table, using specific dates in the DSum won't work...since the DSum needs to look at the MthDtSale for each record and calculate the 3 months sum from this date. The date in MthDtSale will always be the 1st of the month by the way.

What you've proposed in your example would show 1022 for every record...wouldn't it?

Thanks.
 
Upvote 0
I see, you want a running sum. I don't think that you can use DSum() then. Simplest is to use Access reports which have a running sum feature. Otherwise you have to write a query that uses a subquery.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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