Show Fiscal Year to Date Totals

fiscal year to date totals

Last week, you saw how to calculate the fiscal year and month, by adding formulas in a pivot table’s source data.

The formulas referred to a named cell – FYStart – where you enter the start month of the fiscal year.

pivotfiscalyear01

Total by Fiscal Year and Month

With those calculations in the source data, you can put them into the pivot table, to show totals for fiscal year and month.

In the screen shot below, you can see the total for each fiscal month, over a 3 year period, and the grand total for each fiscal year.

pivotfiscalyear08

Show Fiscal Year to Date

If you add slicers for the Fiscal Year and Fiscal Month fields, you could filter the pivot table, to show a specific fiscal year and month. In the screen shot below, it’s showing fiscal year 2014, months 1, 2 and 3 (April, May and June).

pivotfiscalytd01

Show Fiscal Month to Date

However, if you want to focus on the current fiscal month, and filter for month 3 (June), the Fiscal Year total will only show the June total.

So, you can see how you’re doing this month, but the year to date total is lost.

pivotfiscalytd02

Calculate Fiscal Year and Month to Date

To show both the Fiscal YTD and MTD amounts in the pivot table, you can add two more columns to the source data. These formulas are based on the ranges named FY_Sel and FM_Sel, which show the year and month that you selected.

pivotfiscalytd03

Here is the formula to calculate the FYTD amount:

=SUMIFS([@TotalPrice],[@FY],FY_Sel,[@FM],”<=” & FM_Sel)

And here is the formula to calculate the FMTD amount

=SUMIFS([@TotalPrice],[@FY],FY_Sel,[@FM], FM_Sel)

Both formulas use the SUMIFS function, to show the amount based on multiple criteria.

Show the Totals in the Pivot Table

When you add the FYTD and MTD fields to the pivot table, you can see the full picture, with both fiscal year to date and month to date amounts.

In the source data, the formulas automatically update when a different month or year is selected, but the pivot table doesn’t refresh automatically.

  • You can right-click on the pivot table, and click Refresh.
  • Or, use a macro to refresh the pivot table – that is what happens in my sample file

pivotfiscalytd04

Download the Sample File

To see all the calculations, and how the pivot table works, you can download the sample file from my Contextures website.
NOTE: This is the same file as last week’s sample, so you won’t need to download it again, if you already have that one.

To download it, go to the Pivot Table Fiscal Year page, and use the download link. The zipped file is in xlsm format, and contains a macro that updates one of the pivot tables.

____________________

One thought on “Show Fiscal Year to Date Totals”

  1. Pingback: Excel Roundup 20140623 « Contextures Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.