PowerPivot, Running Totals, and Chart Lines with null data


New Member
Oct 3, 2014
Good afternoon all,

I'm certain that we've all had this issue at one point or another but I can't seem to find a solution--hoping that someone has something ready to go.

Here's the summary:

PivotChart with Date values on X AXIS and a Running Value Count 'flattens' when max is reached.

The scenario is as follows:

The Date values are a week ending series from 10/2019 to 1/2020. The Counts are Finish Dates populated in another application. The goal is to have the line 'cut' when null data is reached. Since a finish date in the future would also indicate an error, we can also interpret this to mean that we could stop drawing the line at the current date.

Typical methods of hiding NA in the series do not function as the data model originates in PowerPivot. I'm experimenting with a VBA function to 'fake' it by turning the forward (greater than TODAY) segment of the line transparent/no fill (which will fulfill the requirement) but before I devote my day I wanted to see if the community already had this in their pocket.

I'll be happy to provide more details if necessary, thanks so much for taking a look!
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics