Forum Discussion
Multiple Fiscal Year Pivot Tables in one Chart
Our Fiscal Years start on April 1, therefore when pulling data for various Fiscals I use the Date Filter "Between". If I want 5 Fiscal Years' data I then create 5 different PivotTables with this filter (I know of no other way to make this happen)
I now want to take the data from the 5 different fiscals and create a Chart. However, the data in one chart shows up a two bars (ex. 2020 and 2021) because our fiscal crosses between the years. I've tried EVERYTHING to get rid of the two years but nothing works.
How do I possibly create a chart with only 5 Bars, each labelled 2016/17, 2017/18, 2018/19, 2019/20, 2020/21 ???
Thank you if you know how to easily solve this issue!
kajeB Based on the date, add a column for the fiscal year/month and base the pivot table on that column. So, rather than using real dates that Excel tends to groups by year and month, just use the fiscal year label. That could be like "FY16/17-01" through "FY16/17-12" for April 2016 through "March 2017. Or, have two columns. One for the fiscal year and one for the month (1 to 12).
5 Replies
kajeB Based on the date, add a column for the fiscal year/month and base the pivot table on that column. So, rather than using real dates that Excel tends to groups by year and month, just use the fiscal year label. That could be like "FY16/17-01" through "FY16/17-12" for April 2016 through "March 2017. Or, have two columns. One for the fiscal year and one for the month (1 to 12).
Thank you! This seems like such a logical answer and I tried it but the column doesn't show up in the PivotTable Fields for me to create a chart.
I really wished this had worked!kajeB Did you rebuild the pivot table from scratch?
