Pivot table help

[Jamma]

Hi,

Each week I need to report the departments numbers to the stakeholders and they require the last 12 weeks of data split into last 6 weeks and previous 6 weeks.

Is there a way for the tables to move along each week when I add new data without having to go in and adjust the dates?

Thank you.

 

[SpillerBD]

While there is a filter for This Year, This Month, Previous Month... etc, not for your specific case.
However, I have had the same conundrum. You can create a macro to update the filters based on a date calculation.

I use a calculation on a separate sheet.
For the below code, the cell feeding the EndFilterDate is just a TODAY() function and the BeginFilterDate is a calculated field. I then feed that to a number of PivotTables through a link TimeLine Slicer. I do not have it automatically run in any way to avoid conflicts if a user wanted a non-standard date range.

Sub Use_Defined_Date_Range()
'
' Macro13 Macro
'
Dim BeginFilterDate As Date
Dim EndFilterDate As Date

BeginFilterDate = Worksheets("Charting Date Range Selector").Range("B3").Value
EndFilterDate = Worksheets("Charting Date Range Selector").Range("C3").Value

    ActiveWorkbook.SlicerCaches("NativeTimeline___Date").TimelineState. _
        SetFilterDateRange BeginFilterDate, EndFilterDate
End Sub

 

[Jamma]

That is brilliant, thank you very much,