I have a pivot table that is based on data in a folder. In this folder there are various Excel tables that always represent a month. These Excel tables show account transactions with type, group, category, posting text, date and amount. These are then merged using Power Query so that all the data is in a new Excel table. Based on this data, the pivot table is created, which shows the individual months in the columns and is divided into the individual categories in the rows. The cash flow is calculated in the bottom row as the overall result by subtracting the expenses from the income. If, for example, the data from the month of December is not yet available, then this column is not available.
The aim was to create a chart that shows the cash flow without setting filters in the pivot table.
To do this, I wrote down the individual months in 12 rows and extracted the corresponding total result in the row next to it using the formula =PIVOTDATENZUORDNEN("Amount";$A$3; "Months";1) as an example for January. Using this data, I can display a diagram that shows the individual months on the X-axis and the individual amounts on the Y-axis. However, if December does not yet exist in the data, then December is set to 0 because it lacks the data. This field then contains #BEZUG!
How can I ensure that this diagram only shows the months that are actually present in the pivot table?
I thought about saving the months in a dynamic list, but that didn't work.
Basically I tried to extract the months from the pivot table, but I can't access the column labels of the months directly.
The aim is that if a month does not exist, it is not displayed in the chart. it should not be a pivot chart!