Date filter on Pivot table Latest Month

[MarkCBB]

Hi there,

I am looking for a way to alway have the latest month being filtered by a pivot table, Currently it is filtering by last month however I am not getting the data all the time every month, so we are reporting on the latest information.

I am not sure that this can be done on a Pivot table? if so how?

My only other idea is to build a table with formulas to get the Max date value, then work backwards,and then a Sumif to get the values from the dates, but I am not sure how to get around the Months as they all have a different amount of days, so by tables (and Charts) are showing the 31st of Jan in the Feb chart?
does anyone know what I can do to my formula to bring back an NA if the date is not in the same month as the Max Date?

 

[Herbert Seidenberg]

Excel 2010 PivotTable
Show latest, not last, month.
With source helper column and Value Filter.
http://c3017412.r12.cf0.rackcdn.com/04_21_11a.xlsx
If you get *.zip, don't unzip, just rename *.xlsx