Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
ASO Member formula for calculating Average

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?
Answers
-
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.