• cmw 66135 - Friday, November 17, 2017 8:25 AM

    Thanks MMartin. Sadly that went right over my head. Sorry im very green at SQL reporting 🙁

    No problem. I created some test data to better illustrate my point 

    create table #table (    customerID int,
                            salesAmt decimal(10,2),
                            orderDate date
                        )    
                        
    insert into #table(customerID,    salesAmt,    orderDate)
    values
    (15652,20,'2017-01-01'),
    (15652,20,'2017-02-01'),
    (15652,20,'2017-03-01'),
    (15652,20,'2017-04-01'),
    (15652,20,'2017-05-01'),
    (15652,20,'2017-06-01'),
    (15652,20,'2017-07-01'),
    (15652,20,'2017-08-01'),
    (15652,20,'2017-09-01'),
    (15652,50,'2017-10-01'),

    (15675,20,'2017-01-01'),
    (15675,20,'2017-02-01'),
    (15675,20,'2017-03-01'),
    (15675,20,'2017-04-01'),
    (15675,20,'2017-05-01'),
    (15675,20,'2017-06-01'),
    (15675,20,'2017-07-01'),
    (15675,20,'2017-08-01'),
    (15675,20,'2017-09-01'),
    (15675,70,'2017-10-01'),

    (27059,20,'2017-01-01'),
    (27059,20,'2017-02-01'),
    (27059,20,'2017-03-01'),
    (27059,20,'2017-04-01'),
    (27059,20,'2017-05-01'),
    (27059,20,'2017-06-01'),
    (27059,20,'2017-07-01'),
    (27059,20,'2017-08-01'),
    (27059,20,'2017-09-01'),
    (27059,80,'2017-10-01')

    SELECT t.customerID, sum(t.salesAmt) as totalSalesAmountLastMonth,
       ca.totalSalesAmount as totalSalesAmountYTD
    FROM  #table as t
       CROSS APPLY
       (
                SELECT customerID,
                        sum(salesAmt) as totalSalesAmount
                FROM  #table
                WHERE 
                        year(orderDate) = year(getdate())
                GROUP BY customerID
       ) as ca
       
    WHERE 
       year(orderDate) = year(getdate()) and
       month(orderDate) = month(getdate())-1 and
       ca.customerID = t.customerID
       
    GROUP
         BY t.customerID, ca.totalSalesAmount  

    There is no need here to create anything within SSRS as the YTD field is here and ready to be pulled.

    ----------------------------------------------------