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

  • 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

nvgroups

New Member
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:
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
 
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.
 
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.
I do not have Financial Year in my data. I can create the same, do you know any formula. Thx
 
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)
 
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)
I added a new column for FE values and updated pivot for my purpose. Thanks!
 
Back
Top