MAT over a calculated measure

by DeepButi   Last Updated September 28, 2017 16:06 PM

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?

Tags : ssas olap

Related Questions

Get distinct count on calculated member?

Updated April 03, 2015 21:19 PM

Filter hierarchal dimension by members

Updated October 09, 2016 09:02 AM

SSAS where are olap cubes stored

Updated July 10, 2016 08:02 AM