Link to home
Start Free TrialLog in
Avatar of tconnelly321
tconnelly321

asked on

Handling division by zero errors in MDX AND SSAS

I am new to SSAS, and am trying to add a new calculation to my cube to get change %  and I am trying to avoid divide by zero in  the following MDX:

WITH
MEMBER [Measures].[Prior Year MTD] AS 'SUM(PERIODSTODATE([AR Invoice Date].[Calendar].[Month Name],ParallelPeriod([AR Invoice Date].[Calendar].[Month Name],1,[AR Invoice Date].[Calendar].CurrentMember)),[Measures].[Gross Margin Sales Base])',format_string="$#,###.00"

MEMBER [Measures].[Current Year MTD] AS 'Sum([Measures].[Gross Margin Sales Base])',format_string="$#,###.00"

MEMBER [Measures].[$ Change MTD] AS '[Measures].[Current year MTD]-[Measures].[Prior year MTD]',format_string="$#,###.00"

MEMBER [Measures].[% Change MTD] AS '[Measures].[$ Change MTD]/[Measures].[Prior year MTD] ',format_string="#.00%"

SELECT
NON EMPTY { [Measures].[Prior Year MTD],[Measures].[Current Year MTD],[Measures].[$ Change MTD],
[Measures].[% Change MTD]}

 ON COLUMNS, NON EMPTY { ([Customer].[Locations].[Customer State].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [AR Invoice Date].[Month Number].[All] } ) ON COLUMNS FROM [Sales_GrossMargin]) WHERE ( [AR Invoice Date].[Month Number].[All] )


The issue seems to be with how the MEMBER [Measures].[% Change MTD] AS '[Measures].[$ Change MTD]/[Measures].[Prior year MTD] ',format_string="#.00%" handles the division by 0 or nulls. My question is can I add some kind of case statement to this code deal with this ?

User generated image
ASKER CERTIFIED SOLUTION
Avatar of tconnelly321
tconnelly321

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial