0

I am creating a report that shows the performance on a monthly basis.I have historic data from 200101. When I create and preview the report all data shows from 200101 to most recent month.

I want to limit the number of months to be the most recent 13 months.

I tried using this expression 

=max(Fields!month.Value)-13

This doesn't work and shows an error.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
AKR
  • 13
  • 5

2 Answers2

1

What about a DATEADD() function? (If I'm understanding your question)

=DATEADD(DateInterval.Month,-13,MAX(Fields!Month.Value))

Other option would be to filter the data out either in SSRS or your SQL.

The SSRS filter would use between TODAY and the DATEADD() function (=DATEADD(DateInterval.Month,-13,TODAY))

Or filter in your SQL:

WHERE Month BETWEEN SELECT DATEADD(MONTH,-13,GETDATE()) AND GETDATE
BJones
  • 2,450
  • 2
  • 17
  • 25
0

Ideally your expression should be 

=max(Fields!month.Value-100-IFF(Fields!month.Value%100>1,1,88))

100 is for 1 year and 1 is for the month; 88 is actually 100-12 for the case when month is like 201701, then you'd need your value to be 201512 (going back 13 months)

For more checks, we do need to check that we do have 13 month data starting from 200101

=IFF(Fields!month.Value>200201,max(Fields!month.Value-100-IFF(Fields!month.Value%100>1,1,88)),200101)
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60