I have the following Calculated measure -
CREATE MEMBER CURRENTCUBE.[Measures].[Act - Fcst Lbs] AS
case when [Measures].[Act- Fcst Flag]= 0
then [Measures].[Invoiced Pounds]
else [Measures].[DMP - Forecast Lbs]
end,
FORMAT_STRING = "$#,##0;($#,##0)",
VISIBLE = 1 , DISPLAY_FOLDER = 'Calcs' , ASSOCIATED_MEASURE_GROUP = 'DMP Forecast';
works great. however when I display with our calendar hierarchy Dimension, the months will display the correct values, but the subtotal on Year will not be correct. I know its because the calculation is occurring on the Year line. I've tried a Scope statement to override the year, which works IF you select all the months in the year. but if you select just a couple, then the total is not correct.
If the source if the values were not in 2 different places I'd take care of this in the SQL source table.
Our Calendar Dimension Hierarchy is as follows
[Calendar].[Fiscal],[Fiscal Year].[Fiscal Month]
Any directed would be grateful