• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic rolling date filter on Pivot Table??

J

jason

Member
So I have several PivotTables (and about to make several more) that are looking at data from the 'past 6 months'. right now, i have the Date in my column headings and manually check and uncheck the correct months. meaning, every month, i have to go to each chart and add the current month and remove whichever month is beyond 6 months. for right now, these charts are looking at data from October to March. but now that we are in April, i'll have to deselect October and select April for each chart.


i was wondering if there was some type of formula or another easy way of having this filter automatically updated!?! that way, when May rolls around November is automattically removed and May is added?


EDIT:

i guess i should throw in this little 'wrench'/twist: the date field that im using is a concatenated field showing yyyy_mm; so technically i believe excel sees this as text, right? just saying this out load leads me to believe this wont be possible/easy.
 
Nevermind. i figured out...or at least a work-around


because my YYYY_MM Dates were seen as text rather than actual dates, i decided to create a new column with an IF function that looked at the starting date to determine if it was 6 months (180 days) older than NOW() or not. i had it return text based on the condtion and added that to my PivotTable filter area. now instead of manually selected/deselecting the months, it will only show me the rows that have "last 6 months" in my new field!!!


so far, its working great!!! i'll know for sure by the end of the second week of april.
 
You must log in or register to reply here.
Back
Top