0

I have number of charts that I am creating using a data set. I have one chart which is only supposed to be subset of the data. Essentially It should show data for the next 6 months based on the current month. If i ran it today, it should show the entire month of August 2016 - Jan 2017.

Within the data-set I am returning Year and Month. However; How do I create a filter function? I have tried Fields!Month.Value >= Month(Today()), but it misses Jan 2017. I have also tried Fields!Year.Value >= Year(Today()) AND Fields@Month.Value(Today()). What am I missing? Would I be adding a filter to the chart properties, or the Category groups (Year and Month)?

Help would be immensely appreciated.

StelioK
  • 1,771
  • 1
  • 11
  • 21
a415
  • 359
  • 1
  • 6
  • 23

1 Answers1

3

Create a filter in your chart by right click on it / chart properties and select the Filters tab. Add a new filter with the following settings.

enter image description here

In Expression use:

=CINT(Fields!Year.Value & RIGHT("00" & Fields!Month.Value,2))

If your Month field is an integer from 1 to 12 representing month number and it doesn't include leading zero. Ohterwise if your month field include leading zero use:

=CINT(Cstr(Fields!Year.Value) & Cstr(Fields!Month.Value))

Select Integer and Between for Operator.

In the first Value use:

=CINT(CSTR(Today.Year) & RIGHT("00" & CStr(Today.Month), 2))

In the second Value use:

=CINT(CSTR(Today.AddMonths(5).Year) & RIGHT("00" & CStr(Today.AddMonths(5).Month), 2))

It should filter the rows to use in your chart to dates between 201608 and 201701 if you run the report in this month.

Let me know if it helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48