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].CurrentMe mber)),[Me asures].[G ross 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].[C ustomer 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 ?
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].CurrentMe
MEMBER [Measures].[Current Year MTD] AS 'Sum([Measures].[Gross Margin Sales Base])',format_string="$#,
MEMBER [Measures].[$ Change MTD] AS '[Measures].[Current year MTD]-[Measures].[Prior year MTD]',format_string="$#,##
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].[C
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.