We have an OLAP SSAS cube with budget data on the fact table, and budget cathegory (sales, costs, salaries, ...) on a dimension. We have a bunch of formulas for our users but we are not able to create a MAT (moving annual total) on an already calculated measure.

create MEMBER CURRENTCUBE.[Measures].[Sales budget]
as ([Dim budget].[Level_2].[Sales],[Measures].[Budget]);
-- Budget accounting only the values from the fact table with value "Sales" on the appropiate field of the cathegory

create MEMBER CURRENTCUBE.[Measures].[Sales budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].
[Sales budget]);
-- A MAT over a calculated measure doesn't work, we obtain a #VALUE! error

create MEMBER CURRENTCUBE.[Measures].[Budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].[Budget]);
-- A MAT over an original measure works as expected.

Any idea what are we doing wrong? How can we calculate this MAT?

closed as off-topic by Paul White Sep 29 '17 at 10:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Sorry for being off-topic. I cannot see why but sure I'm wrong. It's a specific question about OLAP SSAS cube MDX formula. Anyway I already found the problem. Thanks – DeepButi Oct 2 '17 at 11:13

I had a blonde moment! Just using SUM instead of AGGREGATE and everything is ok as expected :(

Not the answer you're looking for? Browse other questions tagged or ask your own question.