Last Year Calculated Member

Question

Hi,

I have a last year Calculated member below;

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY Act Net Weight]
 AS Sum({ParallelPeriod([Time].[Time By Month].[Year],1,[Time].[Time By Month].CurrentMember)},([Measures].[Act Net Weight])),
FORMAT_STRING = "#,#",
VISIBLE = 1;          

The formula works if 1 time member is selected, i.e. if its a year, quarted, month or day. However if I have multiple selections say in Excel at filter level I tick both Jan and Feb, then the CM is displayed as blank.

Is there a way to fix the CM to work out multiple selections?

Thanks,

B.

Answer 1

Hi BSaliba,

Looks like it's a limitation of SSAS. Please see below thread which was discussed couple of days back -

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d9e19d55-6539-4554-89d7-6494a999fe3d

HTH.

Regards,
Santosh

---

It feels good if you give us some points for helpful post. :)

Answer 2

Thanks,

Will do.

B.

Answer 3

for getting all the days, weeks, months, quarters, semesters and years use the Currentmember.Level in case statement sure it'll work fine..:)

---

Vijay Kolisetty

Answer 4

Could you explain further Vijay please?

 

Possibly with an example.

 

Thanks,

B.

Answer 5

What you have here is the classic multi-select issue when CurrentMember fails because you have more than one member from the dimension in the slicer axis. Mosha talks about it here - http://sqlblog.com/blogs/mosha/archive/2005/11/18/writing-multiselect-friendly-mdx-calculations.aspx

To fix it, you can rewrite the calculated member expression as

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY Act Net Weight]
 AS Sum( Generate( EXISTING [Time].[Time By Month].[Date], {ParallelPeriod([Time].[Time By Month].[Year],1,[Time].[Time By Month].CurrentMember)} ) ,([Measures].[Act Net Weight])), 
FORMAT_STRING = "#,#", 
VISIBLE = 1; 

 

HTH

 

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 6

Thanks Naveen,

Very interesting article.

Will test and advise,

B.

Answer 7

Thanks Naveen. Worked brilliantly. B.

Answer 8

I would like to re-open this question.

Basically, it seems that the code above using the GENERATE and EXISTING code has a bit of an impact on cube performance. Mainly when opening the cube.

I have isolated the occurrance to the addition of it.

Why would such Calculated Members have such an impact?

Thanks,

B.