How can I change the color of a chart line past a certain date?

Hi Everyone,

I am trying to build a report showing the cumulative revenues for the current year and compare them with last year's.

There are 2 fields, Date and Revenue.

Where it gets tricky is that the revenue field also contains data for future dates (3 months from present date). What I want is for the current year's line to show a different color for future dates. I have tried using a calculated field with the following formula:

IF YEAR([Date]) <= YEAR(TODAY()) THEN 'Blue'

ELSEIF YEAR([Date]) = YEAR(TODAY()) AND [Date] <= TODAY() THEN 'Red'

ELSE 'Orange'

END

The issue, as can be seen in the screenshot below is that the cumulative revenue for the forcast gets reset to 0, whereas I want it to prolong the current year's actual revenue.

Is there an easy way to do it, ideally without using a dual axis approach?

Please find attached a test workbook.

Many Thanks