Running total PIVOT line chart continuing after no data

Occasional Visitor

Hi 

Sorry if this has already been posted, I've been looking all over for a solution. 

 

I have a set of financial data; prior year and YTD.  I am using a running total for the YTD, but it continues on the line chart after there is no data and just shows a straight line.  I am using it for an interactive summary. 

 

Is there a way to stop showing the line when there is no additional data to show? 

 

Any help would be greatly appreciated. 

Screenshot 2022-01-09 220758.png

Thanks

Stephen

1 Reply

@Ste191194 You need to alter the running total formula to return NA() when the current month is empty. That will return #N/A in the data series which will be ignored by the chart.

image.png

Edit: Perhaps a running total formula like below is more elegant than the one shown in the picture.

=IF(ISBLANK(E4),NA(),SUM($E$4:E4))