Slicers + timelines with multiple pivots

1. The architecture

RAW (tblSales / Data Model)
        │
PVT sheet: pvt_Trend · pvt_Region · pvt_Product · pvt_Salesperson · pvt_KPI
        │                        ▲
DASH: pivot charts + KPI cells   │ Report Connections
      Region / Category slicers ─┘ FY slicer · Date timeline

Pivots live on PVT (hidden from viewers); DASH holds only charts, cells referencing pivots (GETPIVOTDATA) and slicers.

2. Build it

1. Create the first pivot from tblSales (or From Data Model if you use relationships) on PVT. Name it (PivotTable Analyze → PivotTable Name: pvt_Trend).
2. Copy-paste it to make the others — copies share the same cache/source, so one slicer can drive all of them. Rearrange fields for each purpose.
3. Create pivot charts from each pivot and cut/paste the charts to DASH. They stay linked.
4. Insert slicers (Region, Category, FY) and a Timeline (OrderDate) from any pivot, cut/paste them to DASH.
5. Each slicer → Report Connections → tick every pivot.

3. KPI cells from pivots

A small pvt_KPI with only Values (Revenue, Profit, Orders) and no rows gives totals for the current slicer selection:

=GETPIVOTDATA("Revenue", PVT!$A$3)

Feed these into the CALC KPI block (Module 3) so cards react to slicers too.

4. Slicer settings for dashboards

• Slicer Settings → Hide items with no data (no greyed-out clutter) and sort order.
• Slicer tab → Columns to fit the layout (e.g. 4 regions in one row), button height ~0.7 cm.
• Duplicate a built-in slicer style → set fonts/colours to your palette → set as default.
• Size & Properties → Don't move or size with cells; Locked unticked if you'll protect the sheet but still want slicers usable (Module 8).

5. Timeline vs FY slicer

Timelines are great for picking months/days but use calendar quarters/years. For Indian FY reporting add an FY column to the data and use an FY slicer; keep the timeline set to Months.

6. Pivot settings so the dashboard doesn't jump

For every pivot (PivotTable Options):

• untick Autofit column widths on update,
• For empty cells show: 0,
• Data → Refresh data when opening the file (if the strategy in Module 2 says so),
• Data → Number of items to retain per field: None (removes old items that no longer exist).

7. When slicers can't connect

Pivots from different sources (a sales Table and a targets Table) can't share a slicer — unless both are in the Data Model with a shared dimension (e.g. dim_Region). Then one slicer on dim_Region[Region] filters both. That's the cleanest way to put actual and target on the same dashboard.

Common mistakes

Pivots on DASH (they resize and break the layout). Building each pivot separately from the source (separate caches, can't connect). Autofit on. Timeline used for FY quarters.