1 of 1 people found this helpful
You can try the following calc:
if [Date]=[Parameter] or dateadd('month',-2,[Parameter])=[Date] then [Value]
Then you can exclude nulls and only have those two months shown. Your parameter will have to return Date values for the dateadd function to work though.
You will need to use a parameter for this. I have broken the steps down into smaller codes for better understanding.
Using the Sample Superstore data as an example, see the suggested solution below.
1. Create a new date field [Date]
This code truncates every date to the beginning of the month, thereby rolling up your dates into an aggregated month year format.
2. Create a parameter [Select Date]
From your requirement, you want this to be a single month/year selection. For this example, I have only made a string list for months in Year 2014.
3. Create a calculated field [Selected Month]
DATE(DATEPARSE("dd/MMM/yyyy", "01"+"/"+LEFT([Select Date],3)+"/"+RIGHT([Select Date],4)))
This code converts the selected string parameter selection into a date.
4. Create calculated field [Two Months Prior]
DATE(DATEADD('month', -2, [Selected Month]))
5. Create calculated field [Date Filter]
IF [Date] = [Selected Month] THEN 1
ELSEIF [Date] = [Two Months Prior] THEN 1
This code will ensure that only the selected month and prior 2 months are displayed. Put this on the filter shelf and set to 1 to filter out null date values.
Just bear in mind that parameters are static and will not update automatically. You may consider having a separate Select Month and Select Year parameters to make it easier to for manual updates.
See attached workbook. Hope this helps.
Sample - Superstore_OO.twbx 1.1 MB