Thanks Thanks:  0
Results 1 to 7 of 7

Thread: DSum not calculating the "correct" value

  1. #1
    New Member
    Join Date
    Sep 2006
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DSum not calculating the "correct" value

    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!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Sep 2006
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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,Date()) & "#")
    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 by xenou; Dec 4th, 2017 at 12:11 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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 |
    ---------------------

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    New Member
    Join Date
    Sep 2006
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,016
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: DSum not calculating the "correct" value

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •