If I'm picturing your scenario correctly, what might be called for here is a calculated field that roughly "if date<intervention-date, return cost, else return null".
If you can then create a reference line based on that field, that could give what you're looking for?
You might possibly be able to achieve what you want with a calculated field something like this:
[preInterventionCost] = IF ([DATE]<[INTERVENTION_DATE]) THEN [Cost] ELSE NULL END
The if you define a reference line in terms of aggregates on your calculated field it should be calculated based on the pre-intervention data only.
I haven't tried it, but it sounds as if it should work. Let us know if it helps. I can think of places where I'd use that trick now I've thought of it (assuming it actually works!).
OK James, you win.
I added a calculation to provide me the average value for the preintervention months. When I drag the calculation onto the same Y axis, it plots another column next to my stacked columns for each month. So now I have two columns per cell. How can I drag it into the chart so that I only see a reference line and not the underlying data?
Put the calculated field on the Level of Detail shelf - you should then be able to select it for your reference line.
Wow. That is so great. Using NULL was also critical rather than adding zero for the excluded (non pre-intervention) months in order to average with the correct denominator.
Too bad there's no way to rank these posts based on usefulness or value. This one is a keeper.