Calculate Cumulative Funding Through a Selected Date in an Excel PivotTable
Question details
Calculate funding accumulated through a selected date or month rather than only the funding for the selected month.

- 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.
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.
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.
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.
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.
Create a second measure: Cumulative Funding := VAR CutoffDate = MAX('Calendar'[Date]) RETURN CALCULATE([Funding], FILTER(ALL('Calendar'), 'Calendar'[Date] <= CutoffDate)).
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.

Calculate year-to-date funding with SUMIFS
Use a worksheet formula when a separate result cell is sufficient and a Data Model measure is not needed.
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. Open the workbook: Open your funding workbook in WPS Spreadsheet and locate the date and amount columns.
- 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. 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. Update the result: Change H2 to another cutoff date and check the formula cell for the updated year-to-date total.

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.




