ASO Member formula for calculating Average

ksdesh

I am trying to write a very simple member formula, but keep getting syntax errors when I verify it.

What I need is average daily revenue for the past 3 months. it does not have to be exact, just sum of past 3 months divided by 90 is good enough.

I have rolling 3 month members in my period dimension called 3M (PerXX) where XX is the period (01 to 12). I am trying to get it to work for just one month for now, will add other months once this works. I have tried the formula below:

CASE

WHEN Contains([Period].CurrentMember, {[Per03]})

THEN (CrossJoin({[A4000]},{[3M (Per03)]}) / 90)

END

This is giving the following error:

Error(1260052) - Syntax error in input MDX query on line 3 at token '/'

I have tried without the CrossJoin. the Syntax verifies but the values don't show up.

What am I doing wrong?

ksdesh

ok, so going through the documentation I realized that Crossjoin is just giving the intersection. So it is not going to actually give the number stored in that intersection. So I modified the formula to the following:

CASE

WHEN Contains([Period].CurrentMember, {[Per03]})

THEN( Sum(CrossJoin({[A4000]},{[3 MTD (Per03)]})))

END

But it is not giving me any data in the member.