I'm probably making this more complicated than it needs to be but I can't see it anywhere. I'm trying to produce a chart for the year that uses actual figures until the selected month and then the forecast beyond that to year end. The expression I have a the moment is:
=If(????,Only({<Month>}MarginForecast),Only({<Month>}KPIMargin))
And I'm struggling with what to put in the If statement.
The data looks like this (dummy data):
MonthYear | Total Stream Revenue Planned | Total Stream Revenue Forecast | Total Stream Revenue Reforecast | Total Stream Revenue Actual | Total Stream Costs Planned | Total Stream Costs Forecast | Total Stream Costs Reforecast | Total Stream Costs |
Jan-18 | 500 | 500 | 500 | 450 | 400 | 400 | 400 | 423 |
Feb-18 | 500 | 500 | 400 | 400 | 400 | 400 | 400 | 497 |
Mar-18 | 500 | 500 | 500 | 350 | 400 | 400 | 400 | 400 |
Apr-18 | 500 | 400 | 400 | | 400 | 200 | 400 | |
May-18 | 500 | 400 | 400 | | 400 | 200 | 200 | |
Jun-18 | 500 | 450 | 450 | | 400 | 200 | 200 | |
Jul-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
Aug-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
Sep-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
Oct-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
Nov-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
Dec-18 | 500 | 300 | 300 | | 400 | 200 | 200 | |
The relevant bit of the script is:
[Total Stream Revenue Planned] - [Total Stream Costs Planned] as MarginPlanned,
[Total Stream Revenue Forecast]-[Total Stream Costs Forecast] as MarginForecast,
[Total Stream Revenue Reforecast]-[Total Stream Costs Reforecast] as MarginReforecast,
[Total Stream Revenue Actual]-[Total Stream Costs] as KPIMargin,
[Total Stream Revenue Actual] as Revenue,
[Total Stream Revenue Planned] as RevenuePlanned,
[Total Stream Revenue Forecast] as RevenueForecast,
[Total Stream Revenue Reforecast] as RevenueReforecast,
[Total Stream Costs] as Costs,
[Total Stream Costs Planned] as CostsPlanned,
[Total Stream Costs Forecast] as CostsForecast,
[Total Stream Costs Reforecast] as CostsReforecast,
If a month is selected I get:
![](/legacyfs/online/200168_pastedImage_0.png)
I can get this with no month selected (which is what I want it to look like), but it messes up 17 other items on the dashboard:
![](/legacyfs/online/200169_pastedImage_1.png)
Does anyone have any ideas?
Thank you