none
Pivot table, Fiscal Year not Calendar Year

    Question

  • Hi,

    I have a pivot table based on about 30,000 rows or transactional data, each carrying a date field.  I want the columns to be Year(MyDateField), Quarter(MyDateField), Month(MyDateField) and Day(MyDateField.  It works beautifully... except I need it in fiscal years (Apr x1 to Mar x2) and it is working in calendar years.  To get around it I derived the financial year and financial quarter from the date field as separate columns in the underlying query. When I then added Month(MyDateField) from the field list the right of the Financial Quarters column I had preserved the drill down feature.  From the Months(MyDateField) I could also to drill down and filter out specific years, quarters, months and even days (albeit in the calendar year grouping)!  Hooray!

    However, I can't help but thinking that this must be such a common issue with so many (I would have though the majority of) companies not working to a calendar year that there would be built in way to set the "start of year" date...?  Or perhaps at least a smarter way of doing it than I have.

    Charles

    Thursday, December 06, 2012 7:04 PM

Answers

  • Dear all,

    Imb, that is pretty much what I had already done, and it works, but just not quite as well as Access handles a calendar year.

    Ken, I like you function, but my question is not how to return the start of the year, it is how you set get Access (or Excel for that matter) to base it's year functions around a start and end date that is not 1 Jan and 31 Dec respectively.

    Thanks again though for all your input, Ken I will be saving your function as it will no doubt come in handy at some point.  BTW, I am in the UK and always thought "Fiscal" meant all things financial.  However, I looked it up and indeed it is: "Of or relating to government revenue, esp. taxes."  Thanks for shining a light on that ;-)

    Regards,

    Charles
    • Marked as answer by Charles__Toray Wednesday, December 12, 2012 5:33 PM
    Wednesday, December 12, 2012 4:56 PM

All replies

  • Use DateAdd function to offset the calendar date the correct number of months.
    Thursday, December 06, 2012 7:19 PM
  • Hi Karl,

    All that will do is return an incorrect date.  The dates are correct, it is the grouping of the dates that is the issue.  I want my dates left alone, but their grouping to be adjusted so that April is the first month of the year and March is the last.

    Thanks,

    Charles


    Friday, December 07, 2012 9:38 AM
  • However, I can't help but thinking that this must be such a common issue with so many (I would have though the majority of) companies not working to a calendar year that there would be built in way to set the "start of year" date...?  Or perhaps at least a smarter way of doing it than I have.

    Hi Charles,

    Does it help if you sort your data by  Year(this_date),Month(this_date)?

    Imb.

    Friday, December 07, 2012 9:46 AM
  • Dear Imb,

    It works the way that I have done it, sorting as desired.  The thing is I am having to create a column in the underlying query for Fiscal Year and Fiscal Quarter.  However, if my company ran their financial year in line with the Julian calendar year, i.e. the first and last days of the year are 1 Jan and 31 Dec respectively, the standard tools would do all that work for me, and they would do it way better.

    Try it.  Create a pivot table with a query that includes a date filed as its data source.  In the field list of the pivot table, note the date field.  It will have "YourDateField by Month" as an expandable item.  Expand it and add the Year(YourDateField) item from the field list to the column area, then do the same for Quarter(YourDateField) and Day(YourDateField).  Now, click the filter on the column names and see how you can filter, quite elegantly, a whole year or quarter or just a specific day.  Lastly it exports to Excel beautifully (Design Ribon>Export to Excel).

    As soon as you create your own fields you begin to lose all that built in functionality.  So, I was hoping there was a way to tell Access (and hopefully Excel too) "the first day of the year is 1 April and the last is 31 March".  As this has to be a common requirement with so many business having different financial years I would have thought there would be a way to adjust the start and end of years in these business applications.

    I can upload what I have done if it helps to illustrate what I have done and where it falls down in terms of not being as good as when based on a calendar year.

    Thanks,

    Charles
    Friday, December 07, 2012 10:21 AM
  • As soon as you create your own fields you begin to lose all that built in functionality.  So, I was hoping there was a way to tell Access (and hopefully Excel too) "the first day of the year is 1 April and the last is 31 March".  As this has to be a common requirement with so many business having different financial years I would have thought there would be a way to adjust the start and end of years in these business applications.

    I can upload what I have done if it helps to illustrate what I have done and where it falls down in terms of not being as good as when based on a calendar year.

    Hi Charles,

    I can understand the problem that you have, but alas, I have no direct answer for it.

    One of the reasons is that I have practically no experience with pivot tables: I do not use the Access functionality. I make analogous reports, but in a different way.

    Another reason is that I use only very limited standard Access functionality. What I try to do in my approach, is to described everything in any application in a generalised way, so that it works for any application. The differences between the different applications are thereby defined in definition tables. For this purpose I have developped quite a lot of code, shared by all applications. The consequence is that I can work with any range of time periods.

    But back to your problem. Is it possible to make use of a kind of translation table between the gregorian calender and your fiscal calender?  Probably you can link it in the query, or use it as a reference.
    It could be something as:

            Startdate         Monthnr
         1-apr-2012          201201
        1-may-2012          201202
         1-jun-2012          201203
                       ...                   ...

    For sorting you can use the Monthnr, for selecting and grouping you can use the definitions in Startdate.
    The start of the fiscal year is the Startdate of the minimum Monthnr in that fiscal year, etc. The last day of the fiscal year is - in my systematics - the Startdate of the next fiscal year - 1.

    Perhaps it gives you some thoughts.

     

    Imb.

     

    Friday, December 07, 2012 5:00 PM
  • I don't know if this will help, but the following function returns the start of the accounting year for any date, passed in as the third argument or defaulting to today if omitted, based on the month and day when the accounting year starts passed into it as the first two arguments:

    Public Function AcctYearStart(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As Date

        Dim dtmYearStart As Date
        
        If IsMissing(DateVal) Then DateVal = VBA.Date
        
        ' get start of accounting year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
            
        ' if date value is before start of accounting year
        ' accounting year starts year previous to date's year,
        If DateVal < dtmYearStart Then
            dtmYearStart = DateAdd("yyyy", -1, dtmYearStart)
        End If

        AcctYearStart = dtmYearStart
        
    End Function

    Provided that the accounting year starts on the 1st of a month it should then be a simple task to return the quarter, month etc adjusted to the accounting year, e.g. for today 2012-12-07 with an accounting year starting 1st April:

    dtmDate = VBA.Date

    ? DateDiff("q",AcctYearStart(4,1,dtmDate)-1,dtmDate)
     3
    ? DateDiff("m",AcctYearStart(4,1,dtmDate)-1,dtmDate)
     9
    ? DateDiff("w",AcctYearStart(4,1,dtmDate)-1,dtmDate)
     35
    ? DateDiff("d",AcctYearStart(4,1,dtmDate)-1,dtmDate)
     251

    I think it would fall down, other than for days, with something like the traditional start date for the UK fiscal year of 5th April, however.  Fiscal year here really means tax year BTW.

    Ken Sheridan, Stafford, England

    Friday, December 07, 2012 9:37 PM
  • Dear all,

    Imb, that is pretty much what I had already done, and it works, but just not quite as well as Access handles a calendar year.

    Ken, I like you function, but my question is not how to return the start of the year, it is how you set get Access (or Excel for that matter) to base it's year functions around a start and end date that is not 1 Jan and 31 Dec respectively.

    Thanks again though for all your input, Ken I will be saving your function as it will no doubt come in handy at some point.  BTW, I am in the UK and always thought "Fiscal" meant all things financial.  However, I looked it up and indeed it is: "Of or relating to government revenue, esp. taxes."  Thanks for shining a light on that ;-)

    Regards,

    Charles
    • Marked as answer by Charles__Toray Wednesday, December 12, 2012 5:33 PM
    Wednesday, December 12, 2012 4:56 PM
  • Ken, I like you function, but my question is not how to return the start of the year, it is how you set get Access (or Excel for that matter) to base it's year functions around a start and end date that is not 1 Jan and 31 Dec respectively.
    The function is just the entry point.  Once you know the start of the accounting year for any given date it's a relatively simple task to make the adjustments to return the equivalent of a built function applied to the year starting on that date, as my examples showed, being equivalent to the use of the DatePart function for the calendar year.  This general methodology of adjustment on the basis of the start of a non-standard date/time period applies not only to dates but also to times; we recently had a post regarding the aggregation of production values for working days running from 05:00:00 to 04:59:59 for instance.

    Ken Sheridan, Stafford, England

    Wednesday, December 12, 2012 7:03 PM