Multiple Fiscal Year Pivot Tables in one Chart

[kajeB]

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!

[#2 · Accepted solution]

@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).

[#3]

@Riny_van_Eekelen 

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!

[#4]

@kajeB Did you rebuild the pivot table from scratch?

[#5]

I did, but I just figured it out - I needed to go to "All" instead of "Active" in the PivotTable Fields.

THANK YOU!! You just made my day 100x better and now I can get this deck out. WHOO!

[#6]

Thank you for your answer and sharing your solution. Without step by step instructions I found this confusing but I'm sure someone would find it valuable!