+ Reply to Thread
Results 1 to 4 of 4

Timeline Slicer custom fiscal period

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Timeline Slicer custom fiscal period

    I am using Excel's 2013 timeline slicer, and need to filter by month(s). The problem is that the monthly reporting period begins on the 15th of the month. The user insists on using the timeline slicer, and does not wish to use the drop down filter on the PivotTable, where he would have to choose the 'Between' option, then set the first & last day. The user will either filter by a single month (e.g. Nov 15 - Dec 14), or by multiple consecutive months (e.g. Nov 15 - Feb 14).

    Here is what I would expect from each of the Timeline Time Levels:
    YEARS - can behave as it normally does, but I am not against this filtering to show the fiscal year
    QUARTERS - (this filter option will not be used) can behave as it normally does, but I am not against this filtering to show the fiscal year
    MONTHS - I would suggest that in my workbook I would have a named range (cell) that accepts my starting day of the month (e.g. 15) Hopefully this could be used as a variable in the code that may be required.
    DAYS - can behave as it normally does

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Timeline Slicer custom fiscal period

    Hi Pinebush,

    I'd create a helper column in the data that would give another breakdown to the dates. Like JanPre15, JanPost15, FebPre15, FebPost15, etc...

    Then use this helper column for your slicer.

    I hope that makes sense.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Timeline Slicer custom fiscal period

    I should have also mentioned that I a filtering a PivotTable. I tried to create a calculated field, but since I am using a Timeline slicer (new to 2013), it will accept only dates. The calculated column was not perceived as a date, therefore could not be used for the Timeline slicer. Maybe if I tried to add the column to the data before it is summarized in the PivotTable ...

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Timeline Slicer custom fiscal period

    That was my idea. Add the helper column before doing the pivot.

    I've also seen creating ranges of dates in a grouping of dates. This might allow you to do what the customer wants.

    See http://www.contextures.com/xlPivot07.html
    and look at the "Number of Days" box. Change it to 15 instead of 7 and see what happens. I don't know if the slicer will like groups.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Generating Custom Timeline on Separate Tab
    By jsboss in forum Excel General
    Replies: 4
    Last Post: 01-01-2014, 07:02 PM
  2. Look Up Fiscal Year and Period from Table
    By bberks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2010, 09:58 AM
  3. Check if final week of fiscal period
    By dvlchd3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2008, 07:50 PM
  4. [SOLVED] Lookup dates, fiscal period table
    By DSCAVOTTO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2006, 12:40 PM
  5. To automatically give fiscal period/fiscal year
    By Turnipboy in forum Excel General
    Replies: 7
    Last Post: 01-19-2006, 05:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1