Have the pivot chart not include the current months data

[rs1aj]

I've built a pivot line chart recording sales of two different items. Today is July 1.

My problem is naturally for the month of July 2015, the lines take a dive because there are no sales recorded yet. How do I keep July 2015 off the chart until the month is complete? I want this to be done automatically because I have it on a business dashboard that I don't want to have to go into the dashboard at the beginning of every month and un-check the new month from the pivot table that is feeding the chart.

Is there an easy way of doing this with formula's or pivot chart settings?

[bhenlee]

add #N/A via a formula result (IFERROR, ISBLANK)

[rs1aj]

My problem is it is a pivot table that is feeding the chart. The other problem is there are sales for the month so it is not blank(isblank) or giving an error(iferror)

As far as I know, I can't add formula's to pivot charts. Am I correct?

I know I can just bypass the pivot table and just build the information I need with formulas alone and include formula's to make the current month zero so they don't appear on the chart but I'm trying to use the pivot table first if I can.

[bhenlee]

then in your pivot table options:

For error values show: #N/A

For empty values show: #N/A

You can still use the formula in your Source Data.

[rs1aj]

I made the changes under the pivot table options to no avail. The totals for July have value. It is not showing error, nor is it empty. The current July 2015 sales for part A is currently $45,248 and the sales for part B is $8,451. With an average of over $1 million in sales a month, the line chart dives for July 2015. I was just trying to avoid the dive for the current month.