This article explains how to calculate the previous period growth in SQL Server Analysis Services.
Original product version: SQL Server
Original KB number: 2406745
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.
Use formula to calculate previous period growth
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
PreviousPeriodCurrentBalancethat 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 topic in SQL Server Books Online: IIf (MDX)
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
