Running total in - hidden columns in chart based on date

nielf

Board Regular
Joined
Nov 13, 2012
Messages
69
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:

 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Derek73

New Member
Joined
Oct 25, 2016
Messages
38
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())
 
Last edited:

nielf

Board Regular
Joined
Nov 13, 2012
Messages
69
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.
 
Last edited:

nielf

Board Regular
Joined
Nov 13, 2012
Messages
69
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,510
Messages
5,469,042
Members
406,631
Latest member
hotspot1972

This Week's Hot Topics

Top