Problem in 'Rolling 12 Paid Months' member calculation

Question

I have a calculated measure like: Measure C = MAX(Measure A) / Distinct count(B).

I need to create a calculated member called 'Rolling 12 Paid Months' which is created as :

CREATE MEMBER CURRENTCUBE.[Reported Paid Date].[Paid Date].[Rolling 12 Paid Months] as AGGREGATE(  

    STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].[" +  

        [Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]")  : 

        STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].["  + 

        [Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]" ) .lag(11)

)

The problem here is that when browsing the member 'Rolling 12 Paid Months' in rows and measure 'Measure C' in columns, it is not aggregating the value. Instead, the monthly calculated results are summed up giving a large value. 

Please provide me a solution in this. Do we need to modify the MDX of how the calculated member 'Rolling 12 Paid Months' is calculated?

 

Thanks in advance,

Anish Tuladhar

 

 

---

Anish Tuladhar -Do not forget to mark as answer, only if helpful.:)

Answer 1

Hi Anish,

Yes, you need to modify the MDX. I don't understand the part of "Distinct count(B)", so below mdx only includes MAX(MeasureA). I think it will be easy for you to correct it by including the "Distinct Count(B)" logic -

CREATE MEMBER CURRENTCUBE.[Reported Paid Date].[Paid Date].[Rolling 12 Paid Months] as

MAX(

{STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].[" + 

        [Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]")  :

        STRTOMEMBER( "[Reported Paid Date].[Paid Date].[Paid Months].["  +

        [Reported Paid Date].[Cycleend].children.item(0).properties("key") +"]" ) .lag(11)
},
MeasureA
)

the bold parts are different from your MDX.

Please let me know if you have more questions.

Regards,
Jerry

Answer 2

Hey!!!!

I found the solution

Measure C = MAX(Measure A) / Distinct count(B).

CREATE MEMBER CURRENTCUBE.[Measures].[Member Age]
 AS SUM([MemberForAvgAge].[Member Id Int].[Member Id Int].MEMBERS, [Measures].[Max Member Age]), 
VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[Measures].[Average Age]
 AS IIF([MEASURES].[Member Count] = NULL,
NULL,
[MEASURES].[Member Age]/[MEASURES].[Member Count]
), 
FORMAT_STRING = '#,##0.0', 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Enrollment';  

Sorry i donot have much time to translate the code in generic format. But this logic works!!!

Calculating the Max measure as a simple measure and scoping it in the calculated section is quite different from making the max measure calculated one in the calculated section. This is because all the calculated values are determined in the calculated section during the query run time.

Thanks,

Anish

---

Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)