mdx - Working with an OLAP Cube in Excel; determine monthly values within a larger date range? -

i've been working in olap cube pivottable in excel while now, more i'm trying integrate calculated measures streamline things, , i've hit wall.

the cube has date filters set specify month & year (they more specific, finer levels aren't used).

the rows list individual projects, , values can reflect 2 measures: 1 reflects average score date range, , reflects number of observations average based on.

i create calculated measure display average score each project long 2 criteria met:

1. there minimum of 100 observations whole date range
2. there no months 0 observations across date range specified in pivottable.

i should clarify date ranges use vary , length, , not end recent month, in increments of whole months.

i'm part of way there, calculated measure based on provide me average if there enough observations date range:

iif([measures].[observations]>=100,[measures].[average],"^^") 

now, need add criteria of no months 0 observations.

i have attempted use count(), in form ignores date range set in pivottable , returns count of of months there value project, including zeros.

count(([calendar].[calendar].[month],[measures].[observations]),excludeempty) 

i tried determining lowest number of observations in month using expression, again ignores date range, , not reflect empty cells:

min([calendar].[calendar].[month],[measures].[observations]) 

i think currentmember need include, can't work me.

assuming it's relevant, , i'm not sure best way explain calendar hierarchy, reflects have:

the first calendar listing 1 used filter pivot table data, , pulled mdx expressions above.

edit:

thanks @sourava reply. tried few things, , given formatting in comments limited, here's rundown of did.

i'm getting error message says "query(1,35) parser: syntax 'with' incorrect"

to make sure i'm using correctly:

after making changes below, pasted whole thing mdx: window 'calculated measures' tool in excel.

i of course changed observations , average variable names in cube.

i changed '[project].[projectcode]' '[project].[projectname]', how cube set up.

on '[newmeasure]' changed name using calculated measure.

on '[your cube]' i've tried couple different things; cube reference use in cube formulas in excel looks this: 'cubename normativecube', tried pasting in brackets , without quotes, leaving normativecube part off, , doing without brackets.

i modified last line after 'where' reflect date ranges this: '[calendar].[calendar].[month].&1&[2015]:[calendar].[calendar].[month].&[12]&[2015]'

i've set '[calendar].[month].[month].&1:[calendar].[month].[month].&[12]'

also, 1 more question; work date range, or intended specified in mdx? need function based on date range set cube filters.

edit 2:

i needed tweak tad changing '> 0' '= 0', original solution showed average did not have data each month in date range, , adjust 1 of calendar set expressions or whatever.

iif  (   [measures].[observations]>=100    ,    count     (      filter         (         exists             (             [calendar].[calendar].[month].members             ,existing [calendar].[calendar].[month].members             )         ,[measures].[observations] = 0         )     ) = 0   ,[measures].[average]   ,"^^"  ) 

edit 3:

found limitation;

the measure works if date range defined cube in whole increment of calendar hierarchy; i.e. single month, single quarter, or single year. doing 2 months, 2 quarters, 2 years, or breaking across 2 quarters or years return false outcome iif() expression.

i played around few different ways set up, can't work.

edit 4:

re: calendar hierarchy

looking underneath [calendar].[calendar] there 4 options: year, quarter, month, & date key.

looking @ members under year, quarter, & month, can drill down way individual day.

the member properties under 3 lists next level hierarchy

on date key, member properties follows: month name month of year time calcs (this doesn't knowledge) week of year week

edit 5:

so worked (finally). must have messed @ point, , editing original formula caused secondary issue having. here's worked me.

iif  (   [measures].[observations]>=100    ,    count     (      filter         (         exists             (             [calendar].[calendar].[month].members             ,existing [calendar].[calendar].members             )         ,[measures].[observations] = 0         )     ) > 0   ,[measures].[average]   ,"^^"  ) 

using existing clause comes in handy @ times when want calculation recognize selection(context). following code self explanatory. let me know if works.

with set zeroobservationmonths filter     (     exists         (         [calendar].[calendar].[month].members         ,existing [calendar].[calendar].[date].members         )      ,[measures].[observations] = 0      )   member measures.newmeasure iif  (   [measures].[observations]>=100 , count(zeroobservationmonths) > 0   ,[measures].[average]   ,"^^"  )  select [project].[projectcode].members on 1, measures.[newmeasure] on 0 [yourcube] ({[calendar].[calendar].[date].&d1: [calendar].[calendar].[date].&d2}) 

edit: if you're planning on creating measure inside excel, have below mdx code in text box "new calculated measure"

iif  (   [measures].[observations]>=100    ,    count     (      filter         (         exists             (             [calendar].[calendar].[month].members             ,existing [calendar].[calendar].[date].members             )         ,[measures].[observations] = 0         )     ) > 0   ,[measures].[average]   ,"^^"  ) 

edit 2: if filtering can happen on attribute, not dates, replace existing [calendar].[calendar].[date].members existing [calendar].[calendar].members in script above.