大网数据广告活动
数据库与应用安全

Calculate previous period growth

· 52CE 我爱测

This article shows that when you calculate the previous period growth in MDX, you need to verify if the previous period exists (for example, if it is not zero or NULL). The other item you need to verify when calculating the previous period growth is to verify if you have any negative values for any of the results. If you do have negative values, you can…

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 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 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.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 07/22/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。