I have a cube and I want to create a MAT column. This column is then expected to show up in the same way as a regular metric would.
How do I create a column that is a Moving Annual Total in SSAS?
A walkthrough / demo would work as well.
+3
A:
Since you haven't really specified anything (MDX or actually in your cube) I will assume you mean in your cube. If i were you I would write a calculated member and then slide it over when browsing or in your reports. It would be something like this
WITH MEMBER [Measures].[Rolling Total] AS 'SUM ( { [Time].CurrentMember.Lag(3) : [Time].CurrentMember }, [Measures].[Warehouse Sales])'
Then you could do something like this: SELECT CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales], [Measures].[Rolling Total]}) ON COLUMNS, {[Warehouse].[All Warehouses].[USA].Children} ON ROWS FROM [Warehouse]
ajdams
2009-10-09 20:00:52
I want to add a column to my cube. I just revised my question.
Raj More
2009-10-10 12:21:14
You mean you want it to be a column in the actual FACT table?
ajdams
2009-10-12 13:06:15
No I do not want to be an actual column - I just want it as a "New Calculated Member"
Raj More
2009-10-12 22:54:00
Then you could make a calculated member similar to the [Rolling Total] and use that.
ajdams
2009-10-13 13:02:08