Search code examples
sqlsql-serversql-server-2005ssasmdx

SSAS - Moving Annual Total

Environment: SQL-Server-2005, SSAS 2005

I am using the following formula to create a Moving Annual Total

SUM ({[Time].CurrentMember.Lag(3):[Time].CurrentMember}, [Measures].[TRx Quantity])

The same column [Measures].[Trx Quantity] is used in other calculations and it works fine. This one, however, comes up with #Value! to indicate some kind of an error. Can anyone point me in the proper direction here?

Solution

  • It turned out to be that I was missing the MONTH

    SUM ({[Time].[Month].CurrentMember.Lag(3):[Time].[Month].CurrentMember}, [Measures].[TRx Quantity])