How to calculate Previous Period Growth in SQL Server Analysis Services


Summary


This article discusses the procedure that you can follow to calculate the correct value for Previous Period Growth when you have Analysis Services cubes that contain negative results.

More Information


In a scenario where you have an Analysis services cube that contains negative results for which you want to calculate “Previous Period Growth” values, you need to apply a formula to get the correct results. For example, assume you have the following balances for years 2008, 2009 and 2010: $10.00, ($4.00), $5.00. Between 2009 and 2010, since the balance went from being negative ($4.00) to a positive value $5.00, the growth has to be positive. To achieve the correct results, you should apply the following formula:

IIF (measures.PreviousPeriodCurrentBalance = 0,    NULL, 
IIF (measures.PreviousPeriodCurrentBalance < 0,
    ([Measures].[Balance Current] - measures.PreviousPeriodCurrentBalance) / measures.PreviousPeriodCurrentBalance  * -1,
    ([Measures].[Balance Current] - measures.PreviousPeriodCurrentBalance) / measures.PreviousPeriodCurrentBalance
)
)



Note: The above formula uses a calculated member PreviousPeriodCurrentBalance that was created on a date hierarchy with the following formula:

([Measures].[Balance Current], [Date Balance].[Hierarchy].currentmember.Prevmember)

For more information IIf function, you can refer to the following topic in SQL Server Books Online.