How to sort category group by another column in SSRS line chart?

Question

I am creating line chart in a paginated SSRS report and my data consists of three columns and looks something like this:

Date       |   MonthYear   |   Percentage
20.09.2018 |   Sep 2018    |   29.8
29.09.2018 |   Sep 2018    |   87.5
30.01.2019 |   Jan 2019    |   18.3

For creating my line chart I need Percentage values per month so my dataset consists of only Percentage and MonthYear.

But I only need to visualize data for the last 10 months. What I have done so far is that I have created another dataset called DateSource containing the Date and MonthYear and filtered the rows such that I get data for the last 10 months.

And in the Category Groups of my line chart I am fetching data as =Lookup(Fields!MonthYear.Value, Fields!MonthYear.Value, Fields!MonthYear.Value, "DateSoure"). Here is what my chart looks like.

I am wondering how to write a lookup query to fetch only those data points whose corresponding MonthYear exist. Or if there's a better way to view data for only last 10 months.

I am new to SSRS and would really appreciate some help.

Answer 1

I figured it out by a workaround. In the original dataset I filtered the rows using IN operator and checked if MonthYear is one from the last 10 months. I used a Split method:

In the code snippet below I concatenate two strings, one containing month's name and another with the respective year. And then check if MonthYear exists IN any of these values.

=Split(
    StrConv(Format(DateAdd("M",-1,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-1,Now())) 
    & "," & 
    StrConv(Format(DateAdd("M",-2,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-2,Now())) 
    ...
    StrConv(Format(DateAdd("M",-10,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-10,Now()))
    , ",")