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.
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.
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).
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.
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.
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
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”