0
=SUM(IIF(Fields!WeekEnding.Value)>= DATEADD(Week,-17,GETDATE()),Fields!Hours.Value / 17)

I want to find the sum of all hours, in the last 17 weeks then divide by 17 to find the average hours per week.

Thanks in advance

Lee
  • 1
  • 2
  • 1
    if you gives us a sample data. that will help to resolve the issue faster. – JonWay Mar 23 '17 at 13:16
  • does the report only show those 17 weeks of data? or does it show more? please show your query and explain the data that shows if you cant provide it – Pants Mar 23 '17 at 13:50
  • so the WeekEnding field is a Datetime e.g. 2017-01-08 00:00:00.000 , I am pulling the data from a view which only has the last 17 weeks in. I want to add columns in a report which shows the Average hours booked over 17 weeks, 16 weeks and so on. The Hours field is a FLOAT. – Lee Mar 23 '17 at 15:28

1 Answers1

0
SUM(IIF(Fields!WeekEnding.Value >= DATEADD(Week,-17,GETDATE()),Fields!Hours.Value,0) )/17
Cyndi Baker
  • 670
  • 8
  • 15
  • Thanks, when I try use this in SSRS, it doesn't like the Week part of the DATEADD - it says Unrecognised Identifier. Any idea why? – Lee Mar 29 '17 at 21:34