Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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:
Does anyone have any ideas?
Thank you