I have the following table structure
| ID | Date | Value | IsForecast
| 1 | 1/1/2017 | $100 | 1
| 2 | 1/5/2017 | $50 | 0
| 3 | 1/9/2017 | $30 | 0
| 4 | 2/1/2017 | $200 | 1
| 5 | 2/5/2017 | $120 | 0
| 6 | 2/8/2017 | $30 | 0
| 7 | 2/9/2017 | $60 | 0
| 8 | 3/1/2017 | $200 | 1
Note the Date column is actually a numerical value that keys off a Calendar table.
I want to basically create a view that shows the "Value" grouped by month, but if there is an Actual value (IsForecast = 0), then use the total of that value, but otherwise use the total if IsForecast = 1.
The result I'd like from the data above would be like this:
| Month | Value
| Jan | $80 (Sum of IsForecast = 0)
| Feb | $210 (Sum of IsForecast = 0)
| Mar | $200 (Sum of IsForecast = 1, because there are no values where IsForecast = 0)
Essentially this is a Fact table, and Date keys off to a Time dimension, IsForecast keys off to a utility dimension to filter Actual Sales and Forecasts.
I have a requirement to show an aggregation that displays Sales and Forecasts in a single column, however if there is an actual sale value use that, and if not use the forecast.
I tried to do this in MDX IIF((Measures.Value, [SalesMode].[Actual]) <> NULL, (Measures.Value, [SalesMode].[Actual]), (Measures.Value, [SalesMode].[Forecast])) but this is playing hell with Grand Totals, because the total of the Sales+Forecasts column always shows the total sales value, rather than the total of the sales+forecasts. If anyone has an MDX suggestion then feel free, but I'm looking back to doing this in a SQL View and just pulling that through as a measure.
You can do it with something like this in a query that groups by month:
If all the rows within a month have isforecast=1, it sums all values. Otherwise, it sums only the values where isforecast=0.
case when min(isforecast) = 1 then sum(value)
else sum(case when isforecast = 0 then value else 0 end)
end