Cell Calculation for cells that already has values

Hi,

I have the table like this

Jan Feb .. Dec

account 50 20 ...

On the cube i would like to make it using calculated cells and that look like this

account 50 70 ...

Same cells has before.

I have tried a few stuff but i cant put values on top of the ones that are already there, so i get the values in the cell and not the values in the calculated cell.

Funny, though, i have put a constant (1) i the 'AS' part of the calculated cell syntax and it adds the cell value with the constant. But if i try to add with the value time.lag(1) SSAS just ignores the value.

Is there a way to do this by calculated cell?

Thank you

Have you considered simply creating a calculated measure using the YTD() or PeriodsToDate() functions? If you did not want the end users to see the underlying measure you could set it's visible property to false.|||

Hello,

The problem with calculated members is that the user would have to change the query when browsing on the cube.

What i would like is that, for the users, browsing on the this account or any other i could use the same measure. And for that reason i need it to be a calculated cell.

Is there a way to solve this?

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

Hello,

This was a good solution, inline with the other solution in the conversation.

I think that i could try altough it will clash a bit with all my other calculated cell. I did not remember to rename the calculated member to the actual measer name.

With all this i assume that the cell calculation doesn't allow this task.

Thank you