Running total in - hidden columns in chart based on date

[nielf]

I'm trying to create a PivotChart like the one below. As we're not yet not into December, I would like to hide the column for month 12 - but still display the target for the month as shown below.

The 'actual' values (the columns) are shown as 'Running Total In' while there's no calculation for the 'target' values (line). When I use 'Running Total In' all of the months will be filled out even though there's no data for the month - and I guess that is also exactly what a 'running total' is supposed to do.

Is there anyway I can hide the columns based on today's date? In the chart above I have simply hidden the column manually.

My data looks like this:

 

[Derek73]

On way to tackle the issue is create another column that calculates your rules to use as a report filter. For example, assuming you only want complete months for the current year, starting in row 2:
<month(today())
<month(today())
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[ code]
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))
'if we're only dealing with this year:
<month(today())

=MONTH(A2)'<'MONTH(TODAY())
<month(today())
'If we're dealing with multi-years in the A column:
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[ code]
=OR(YEAR(A2)'<'YEAR(TODAY()),AND(MONTH(A2)'<'MONTH(TODAY()),YEAR(A2)=YEAR(TODAY())))
'pull the apostrohe's, would't display the code without them
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[></month(today())

Returns TRUE for any month less than the current month. So you'd just set your pivot chart to use a filter of TRUE, and you can always hide this column if it's disruptive.

(It really doesn't like the code display)</month(today())
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[></month(today())
</month(today())

 

[nielf]

Thanks for you reply, Derek.

My initial thought was to use a filter like that. However, the problem is that it hides the entire month:

I would like to display all the months at all times but the columns (actual) should only be visible if we have reached that month. E.g. now that we're in November the chart should display columns for the first 11 months but not for month 12 (even though it is calculated because of the 'running total in' calculation used).

I was wondering if it would be possible to use conditional formatting for this somehow.

 

[nielf]

I found a workable solution.

Instead of using the 'Running Total In' Excel Pivot function I created a helper column calculate the running total. Afterwards I used an IF formula to filter the dates that are greater than today. That way I could 'hide' the colmn for the dates that are in the future.