calculated measure grand total error

0
votes

I have a calculated measure that works, but shows #VALUE! in Grand Total in Browser in SSAS. The Current Forecast for months1-3 have no values and greater than current month Qty Shipped contains no values when I drop those measures in Browser to help debug. 

case 
when [month part] >= [Current Month] then [Measures].[Current Forecast]
when [month part] < [Current Month] then  [Measures].[Qty Shipped]
else 0
end

Any help to what whats wrong

1
ssas

1 Answers

0
votes

Use ISEMPTY() function Syntax

IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(

ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES]. [B])

)