I'm creating a SSRS report that shows projected sales for the next 12 months, grouped by ProductID. While the detail cells are showing correctly, the group sums for each month are displaying all sales for the 12 months rather than just the related month.
For example, here are all the table values for a single Product:
ProductID EstimatedDate ProjSales
123A Oct 10/2017 100
123A Nov 15/2017 100
123A Dec 01/2017 100
123A Dec 31/2017 100
However, this is what the report is currently showing for this Product:
Product EstimatedDate Oct 2017 Nov 2017 Dec 2017
123A Oct 10/2017 100 0 0
Nov 15/2017 0 100 0
Dec 01/2017 0 0 100
Dec 31/2017 0 0 100
Total 400 400 400
As seen above, the detailed cells calculate perfectly as each record in the detail section displays a Projected sales value if the Year/Month matches, otherwise it displays 0. Unfortunately, the final row with the "Total" amounts is incorrect as the monthly cells are showing the total of projected sales for all months rather than just the month in question.
Here are my report expressions for December 2017:
Detail Cell:
=IIF(Year(Fields!EstimatedDate.Value) = 2017 AND Month(Fields!EstimatedDate.Value) = 12, Fields!ProjSales.Value, 0)
Grouped Cell
=IIF(Year(Fields!EstimatedDate.Value) = 2017 AND Month(Fields!EstimatedDate.Value) = 12, SUM(Fields!ProjSales.Value), 0)
Any idea how I can change the grouped expression to retrieve the projected sales for each month?
Edit : format code