# Detect non existent date range in mdx • ### Question

• Hi All
Looking at a weighted averages example on the internet we get:
MEMBER [Measures].[SMA12] AS Avg(
[Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
[Measures].[Internet Sales Amount]
)
But if there was no month 12 months ago, perhaps we would not want to return a value. Can anayone help with how to not carry out the calculation. Have tried the following but its not quite working.
MEMBER [Measures].[SMA12] AS
IIF(isempty( [Ship Date].[Month Name].CurrentMember.Lag(11), [Measures].[Internet Sales Amount]), 0,
Avg(
[Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
[Measures].[Internet Sales Amount]
)
)
Thanks
Monday, May 30, 2011 12:33 PM

• Check if somewhat like this helps you;

WITH
MEMBER [Measures].[x] AS
IIF
(
(
[Date].[Calendar].CurrentMember.Lag(11)
,[Measures].[Reseller Sales Amount]
)
= 0
,Null
,'YourCalculation'
)
SELECT
[Measures].[x] ON 0
WHERE
[Date].[Calendar].[Month].&&;

• Marked as answer by Monday, May 30, 2011 1:40 PM
Monday, May 30, 2011 1:22 PM

### All replies

• Hi All

This has solved the problem for me by creating a flag.

Got the code from:

http://shahamishm.blogspot.com/2008/12/exists-function-mdx-query.html

with member [Measures].[test]

as
iif(isempty((exists ([Date].[Fiscal].currentmember,{[Date].[Fiscal].
[Fiscal Year].&:[Date].[Fiscal].[Fiscal Year].&}).item(0),
[Measures].[Internet Sales Amount])),"Outside","inside")

select {[Measures].[Internet Sales Amount], [Measures].[test]} on 0 ,
[Date].[Fiscal].[Fiscal Year] on 1
from

Unless anyone has a better idea?

Thanks

Bif

Monday, May 30, 2011 1:19 PM
• Check if somewhat like this helps you;

WITH
MEMBER [Measures].[x] AS
IIF
(
(
[Date].[Calendar].CurrentMember.Lag(11)
,[Measures].[Reseller Sales Amount]
)
= 0
,Null
,'YourCalculation'
)
SELECT
[Measures].[x] ON 0
WHERE
[Date].[Calendar].[Month].&&;

• Marked as answer by Monday, May 30, 2011 1:40 PM
Monday, May 30, 2011 1:22 PM
• Thanks again Aniruddha
Monday, May 30, 2011 1:40 PM
• I would think, it is better if you do based on the count of set rather than the measure value=0.

A 0 is a valid value and what if you have the .Lag(11) member a 0 measure value.

```With
Member AvgCarsOwned As
iif({[Date].[Calendar].currentmember.lag(1)}.count=1,
AVG(
{[Date].[Calendar].currentmember.lag(1):[Date].[Calendar].currentmember},
[Measures].[Internet Sales Amount]
),null)
SELECT AvgCarsOwned on 0,[Date].[Calendar].[Month].&& on 1
from
`Update: Just a thought. Did not see it being marked as answered. anyways may be useful`