1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split Pivot and graphs by Financial Year

Discussion in 'Ask an Excel Question' started by nvgroups, Jan 8, 2019.

  1. nvgroups

    nvgroups New Member

    Messages:
    12
    I have data in an Excel spread-sheet for 2017, 2018 with start and end dates.
    The data contains the number of donuts consumed in office by department.

    I created a pivot and a column graph for donuts consumed by each department.

    I need to split the Pivot and graph by Department and Financial year (Apr 2017 to March 2018, Apr 2018 to March 2019). Can anyone provide suggestions.
    Looking for a formulae as the data is large.

    Dept Fin Year 2017-18 Fin Year 2018-19
    ABC 25 30
    BCD 6 15

    Tot 31 45

    thanks
    Last edited: Jan 8, 2019
  2. chirayu

    chirayu Well-Known Member

    Messages:
    959
    You need to change your data to look like below to do that

    Dept Value Year
    ABC 25 2017-2018
    ABC 30 2017-2018
    BCD 6 2018-2019
    BCD 15 2018-2019
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    905
    Seems you have fin year as a field in your pivot. So perhaps...

    If you put that in the filter area of the pivot (left upper quadrant) and then press "Show Pivot Filter Pages" from the Pivot options on the ribbon. For each value of FIN Year you'd have a separate sheet with the filtered pivot. However I don't think it works for the pivot charts.
  4. nvgroups

    nvgroups New Member

    Messages:
    12
    I do not have Financial Year in my data. I can create the same, do you know any formula. Thx
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    905
    Could you upload a sample dataset?
    I believe it is best to add fin year in the raw data if possible, or if you have DAX/PowerPivot you can make the measure when linking the raw to a date table.

    Perhaps in raw data if you have the real date use (it shifts the dates 3 months back)
    FY=YEAR(EDATE(B22,-3))&"-"&(YEAR(EDATE(B22,-3))+1)
    nvgroups likes this.
  6. nvgroups

    nvgroups New Member

    Messages:
    12
    I added a new column for FE values and updated pivot for my purpose. Thanks!

Share This Page