If I understand what you are trying to do, I think there are a couple of options.
1. You could manually hide the year you don't want to show. (right click the header for 2007 and select "Hide").
2. Create a table calc filter that will allow the table calculations to be computed prior to that filtering.
The benefit of #1 is that it is easy, but it does have some drawbacks. One thing I don't like about "Hide" is that there is no visual cue that something is hidden and it is not dynamic. If you do it for 2007 then 2007 will be the value that is always hidden until you change it.
I would prefer Option 2. I've attached your workbook with both options and also a calculated field, which is a table calc for looking up the previous value at the Year level (so it will lookup the previous value for the previous year at whatever level you are aggregating -- quarter, month, etc...).
I'd be happy to answer any questions you might have!
CompareOverPeriod.twbx.zip 85.2 KB
Thanks for your help.
I have few more question. How could I dynamicaly display only last month of data (in our case november 2008). When I had a filter the lookup doesn't work anymore
Create another calculated field similar to the following:
if last()=0 then 'show' else 'hide' end
Place this on the columns shelf next to MONTH(Dates). Right click on one of the column headers that say 'hide', right click and select Hide. Then, right click on the calculation and un-check the Show Header option.
As your data updates, the latest month will show.
Hope this helps!
It works exacly as espected.
This is great, but I want the last month that someone selects on the quickfilter (whichever month and year they choose) to show along with the previous month in order to get the % difference between the both. I have altered the last calculation to be last<=0, but when a user selects a month, only that month shows. I want both to show. Any ideas?