Show Fiscal Year and Month Totals

fiscal year start month

In a pivot table, you can show totals for each calendar year or month:

Group Dialog Box

groupweekmonth03

Group By Fiscal Year and Month

There isn’t a built in way to group by fiscal year and month, but you can add formulas in the source data to calculate those.

I’ve created a sample file that shows how you can do this, and there is a named cell – FYStart – where you enter the start month of the fiscal year.

pivotfiscalyear01

Calculate the Fiscal Year

Then, in the source data, there is a Fiscal Year column that checks that cell, to calculate the fiscal year.

=YEAR([@OrderDate])+(–MONTH([@OrderDate])>=FYStart)

  • The YEAR function returns the year for the order date.
  • If the order MONTH is greater than or equal to the fiscal year start month, 1 is added to the order year. If not, a zero is added.
    (The two minus signs are a double negative, and they convert the TRUE result to a 1)

pivotfiscalyear09

Calculate the Fiscal Month

For the Fiscal Months, there is a lookup table, with a list of all the months, and their calendar month number and fiscal month number. In this example, the fiscal year starts in April.

pivotfiscalyear03

In the source data a Fiscal month formula looks up the fiscal month, based on the order month.

pivotfiscalyear10

Total by Fiscal Year and Month

After those fields have been added to the source data, you can put them into the pivot table, to show totals for fiscal year and month.

In the screen shot below,

  • Fiscal Year is in the column area
  • Fiscal month is in the Row area
  • Total Price field is in the Values area.
  • Category field is in the Report Filter area.

pivotfiscalyear08

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

____________________

5 thoughts on “Show Fiscal Year and Month Totals”

  1. Thanks so much for a wonderful lecture on the subject I have been struggling. I had an idea of using the lookup table.

    Thanks once again, you are a help to all.

    Blessings

  2. It isn’t necessary to use an external reference cell or add columns to the source data which may or may not be practical. Simply add calculated columns to the pivottable itself as follows:

    Fiscal Year: YEAR(OrderDate)+CHOOSE(MONTH(OrderDate),0,0,0,1,1,1,1,1,1,1,1,1)

    Fiscal Month:
    If(MONTH (OrderDate)<4,MONTH(OrderDate)+9,month(OrderDate)-3)

    Sent on my phone so if a syntax error forgive Me!

  3. =MONTH(EOMONTH([@OrderDate];1-MONTH(FYStart)))
    =YEAR(EOMONTH([@OrderDate];1-MONTH(FYStart)))
    where FYStart is the natural measure: 4/1/2013 in this case

    What I would expect though is handling flexible turning dates (from transitions, mergers, establishment periods etc).
    For this one would have to title the financial years and call those names. It would require a max(FYData[Starting date]) with a criterion of “FYData[Starting date]<=[@OrderDate]".
    Do you have a syntax for that one?

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.