I might have misunderstood you but I would just drag Ship Date to Filters. Choose Relative date --> Choose Months, Last 12 months. If you anchor relative to Today, it will be dynamic all the time..
Sharad's suggestion is correct although it would be useful in understanding how you reached this more complex setup than simply creating the filter as suggested. Are you looking for a method that allows for greater flexibility than a simple filter for example, are you wanting to use your parameter to show other date-periods but to be defaulted to the last 12 months?
We can use allowable values as All in parameter and create reports, it will show past and present dates in parameter. Better if you need to show selected date to go last 12 months then this will help.
If you want to show only last 12 months then go with calculation using last()<12.
I have achieved this with relative filters, what I want is there is dropdown filter and then when a user selects a month-year, I should see last 12 months data in the crosstab.
so I know that this can be achieved through a parameter, but with a parameter the problem is that if I take values in a parameter from a field in this case lets say ship date and then use that parameter in calculated field to show last 12 months data it works perfectly.
It fails when lets say data refreshes and the user has to go everytime in the workbook to manually update the parameter.
So I am looking for a dropdown filter not a relative filter, which should work on a single selection and still shows me the last 12 month values based on month year selected.
hope this helps
I get you, basically, you are looking for an option which does not require manually updating such as updating the parameter.
I'm guessing you want to still allow your user to select the start month eg April and your data should filter to the last 12 months of April 15 to March 16 or if they select February then you want to bring through from Feb 15 to Jan 16 etc, is this correct?
This can be easily achieved using a correctly scoped last function - it may even be that the default table down will be sufficient; though you may need to think about how your logic works, which can be using the Month(Today()) function in order to ascertain the starting position for example:
If you want to see the last 12 month from today in a table containing 24 months of data then you filter would be something like (when scoped to month): Last() >=0 And Last() <= 12
The above will only work with hard-coded numbers which is where the logic comes in, in order to determine the correct positioning then you would need to create positioning logic such as:
First off, set your parameter to be integer running 0-12, 0 = current And then Jan to Dec = 1-2
And then create the positioning: [Start] Month(Today()) - <Date Parameter>
[End] 12 + [Start]
So what this does is use your defined month to alter the value of the Last range so you can change your calc to be:
If <Date Parameter> = 0 Then Last() >=0 And Last() <=12
Else Last() >= [Start] And Last() <= [End]
This will still be boolean so will carry a true|false allowing you to simply set true.
Hey, Are you able to solve this issue.
I have the similar problem and looking for suggestions.
I have put together a workbook that displays 12 months of data by inputting a date as a parameter.
Please see if this could be a starting pint to what you want to build.
last 12 months.twbx 455.5 KB
Did you get resolution to the problem, I have a similar one and looking for solution.