Nov 02 2021 11:45 AM
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!
Nov 02 2021 12:02 PM
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).
Nov 03 2021 03:37 AM
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!
Nov 03 2021 03:59 AM
@kajeB Did you rebuild the pivot table from scratch?
Nov 03 2021 04:11 AM
Nov 03 2021 04:36 AM
Nov 02 2021 12:02 PM
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).