Pivot Table Issues

Calculate Cumulative Funding Through a Selected Date in an Excel PivotTable

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Calculate funding accumulated through a selected date or month rather than only the funding for the selected month.

Calculate Cumulative Funding Through a Selected Date in an Excel PivotTable
Product
Microsoft Excel PivotTable and Power Pivot
Device & OS
not provided
Scenario
A user filters funding data by month and wants the total through that month, either across all years or from the start of the selected year.
Observed behavior
The desired result includes earlier funding through the selected date instead of only funding within the filtered month.
Before you start

Confirm that funding dates are stored as real dates and funding amounts as numbers. For the Power Pivot method, use a complete calendar table with a date column related to the funding date column.

Solution 1Recommended

Create a cumulative funding measure in Power Pivot

Use a Data Model measure when the result needs to respond to a PivotTable date filter or slicer.

The measure captures the latest date in the current filter context, then removes calendar filters and totals funding through that date. Other filters, such as funding category, remain in effect.

1
Add the tables to the Data Model

Add your funding table and a complete Calendar table to the Data Model. In Power Pivot, create a relationship from Calendar[Date] to the funding table's date column, and mark Calendar as a date table.

2
Create the funding measure

In the Power Pivot calculation area, create a base measure such as Funding := SUM(FundingData[Funding]). Replace FundingData and Funding with your actual table and amount column names.

3
Create the cumulative measure

Create a second measure: Cumulative Funding := VAR CutoffDate = MAX('Calendar'[Date]) RETURN CALCULATE([Funding], FILTER(ALL('Calendar'), 'Calendar'[Date] <= CutoffDate)).

4
Build and filter the PivotTable

Insert a PivotTable from the Data Model, add Cumulative Funding to Values, and use Calendar date, month, and year fields for rows or slicers. Select a month and year to see funding through the last calendar date in that selection.

Create a cumulative funding measure in Power Pivot
All-time cumulative total: This measure includes funding from earlier years. Use a year-to-date measure instead if the total should restart each January.
Calculate funding in WPS Spreadsheet

Calculate Year-to-Date Funding in WPS Spreadsheet

Use SUMIFS in WPS Spreadsheet to total funding through a date entered in a worksheet cell. This formula produces a separate worksheet result rather than responding directly to a PivotTable slicer.

  1. 1. Open the workbook: Open your funding workbook in WPS Spreadsheet and locate the date and amount columns.
  2. 2. Enter the cutoff date: Enter a date in H2. Use the last date of the month if you want to include the entire month.
  3. 3. Calculate the total: If dates are in B2:B1000 and funding amounts are in C2:C1000, enter =SUMIFS(C2:C1000,B2:B1000,">="&DATE(YEAR(H2),1,1),B2:B1000,"<"&H2+1) in an empty cell. Adjust the ranges to fit your data.
  4. 4. Update the result: Change H2 to another cutoff date and check the formula cell for the updated year-to-date total.
Calculate funding through a selected date with SUMIFSChange the cutoff date without rewriting the formulaOpen and edit Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

Frequently Asked Questions

Why does filtering a PivotTable by month show only that month's funding?

A normal funding sum respects the month filter. A cumulative measure must capture the selected cutoff date and then remove calendar filters before applying the range through that date.

How do I make the Power Pivot measure restart each year?

Use a start-of-year boundary as well as the cutoff: YTD Funding := VAR CutoffDate = MAX('Calendar'[Date]) RETURN CALCULATE([Funding], FILTER(ALL('Calendar'), 'Calendar'[Date] >= DATE(YEAR(CutoffDate),1,1) && 'Calendar'[Date] <= CutoffDate)).

What happens if I select a month without selecting a year?

If the selection includes that month in multiple years, MAX('Calendar'[Date]) uses the latest matching date. Add a Calendar year filter or slicer to make the intended cutoff clear.

Will changing a PivotTable slicer update the SUMIFS result?

Not automatically. The SUMIFS example uses the date in H2, so change that cell separately or use a Power Pivot measure that responds to the PivotTable's calendar filters.