Q:

SQL Sum rows in column based on active date

I have Column value "DateThru" that has a date value that changes depending on the database update day. I have a few tables inner join'd and i'm trying to get a sum of a column (Alloc) up to the "DateThru" date.  I tried limiting the table using
  where ActivityDate <='DateThru'

But i'm getting server errors any time i try in where clause. 
The table I am pulling from looks like this
    Month  ActivityDate  Alloc
    ---------------------------
     x     2017-10-01    .0238
     x     2017-10-02    .0302
     x     2017-10-03    .0156
     x     2017-10-04    .0200
     x     2017-10-05    .0321
     x     2017-10-06    .0123
     x     2017-10-07    .0248

Say "DateThru" is 2017-10-05.
I want to sum Alloc  from 10-1 to 10-5 giving result of ".122" As MTDAlloc.
Can this be done as a "windows function" or in where clause?
Thanks!
I used sum over partition to get the total
I have The total working and organized correctly.  My issue is now i have duplicate entries for each "r".   so when I change the ActiveDate <= DateThru to just "=" it fixes my duplicate issue but does not total the MTDAlloc. I do use a "sum column" to create another so i cant use rownumber() rank to remove duplicates. 
    Month  rep  goal  MTDAlloc
    ----------------------
    x    r1    20    .122          
    x    r1    20    .122  
    x    r1    20    .122          
    x    r2    20    .122
    x    r2    20    .122        
    x    r2    20    .122

The end result will have each unique "r" on one row.
    Month  rep  goal  Alloc
    ----------------------
    x    r1    20    .122         
    x    r2    20    .122 

Really Appreciate the assistance! 

A:

Your problem may merely be single quotes.  From what you describe, this should work:
where ActivityDate <= DateThru

The single quotes make the value a string.  The comparison to a date makes SQL Server want to convert it to a date.  And 'DateThru' is not recognized as a valid date.
EDIT:
My guess is that despite its name, DateThru is really a string.  You can find the offending values by using:
select DateThru
from . . .
where try_convert(datetime, DateThru) is null;

