SSRS - SUM values in a column for part of the dataset (filter condition on date value) H E L P

Question

I have a dataset with 2 different dates in it (current & previous), let's say it looks like this:

DATE     NAME    QUANTITY     MONTH
5/15     John     10           JAN
5/15     John     25           FEB
5/15     Tim      5            JAN
5/15     Tim      15           FEB
5/14     John     9            JAN
5/14     John     20           FEB
5/14     Tim      3            JAN
5/14     Tim      10           FEB    

I created an ssrs pivot like this:

NAME   JAN    FEB     TOTAL
John   10     25      35  
Tim    5      15      20

and now I want to add another column with the previous days total, like:

      <-- New 5/14 total --> <----- This is all 5/15 ---->       

NAME   |   YESTERDAY TOTAL   |      JAN  |  FEB  |   TOTAL
John   |         29          |       10  |   25  |    35  
Tim    |         13          |        5  |   15  |    20

I've tried this expression, but it either returns 0 or returns the total for both days, I don't understand:

=SUM(IIF(Fields!DATE.Value="5/14/2018",Fields!QUANTITY.Value,0))
=IIF(Fields!DATE.Value="5/14/2018",SUM(Fields!QUANTITY.Value,0))

Help would be appreciated. Thanks

Answer 1

Basically you need to use a matrix and a make sure you have two columns adjacent which are then filtered according to your needs.