Without an example workbook this may be difficult to provide much help, However I believe I understand your problem.
When you apply the hide, this would be hiding specific values like month names/numbers and thus when you move the date range it subsequently shows or drops these out accordingly.
What may be worth trying is to have a calculated field to provide a 'Current Month' and 'Previous Months' result Extract the month value from your date selector or date range and assign this as your 'Current Month', with the two months prior to this value as your 'Previous Months' . When you drop this onto your workbook, you will see them segmented. This is when you can apply the hide to the 'Previous Months' result.
Obviously this may have some affect on your current workbook, as well as the majority of your calculations however this allows the workbook to be dynamic against the date range.
*** I would also be careful of the yearly crossover if using month numbers only.
Thanks for your response, this sounds like it should work but I'm having trouble extracting the month value from my date range. Can you provide me with that calculation?
This is entirely dependant on your date. Are you providing a Date Range or a single date ? If you are looking at month on month, then giving the user a filter of Month and Year would work.
If you are using a Range then you will need to code the max month out of that however I would question the reasoning why you would cross over months with a range, to then find the previous two months worth of data for the comparison?
If you have a date range covering say 25th Aug - 5th Sep, Your current month would only show 1-5th Sep, with the August data being hidden as specified. So providing one single point for analysis would work much better. Having a chart showing the date range versus the previous 2 months would inherently incorrect as you are technically double counting, The dates between the 25th Aug - 31st would be in the active chart, As well as in the previous month data, However your request is for the max month.
Can you maybe provide an example workbook or screenshot so I can understand your end goal more fully.
I've figured it out! First, let me clarify and answer your questions:
I'm using a date range and looking at first month vs last month in the range. The data is rolled up into a monthly aggregate so your example of August 25th to September 5th wouldn't apply here.
What I ended up doing was using the Last() function Last()==0 and computed it along months. That way it still preserves the table calculation and only displays the last month in the range.
Thanks for your help Al.