直近の月とその前の月の間の変化率の計算

次に示す例では、現行フィルタに一致するデータの中で直近の月を見つけてから、(現行フィルタに一致するデータの中の)前の月と比較します。

/* This computes the percent change between the most
  * recent month in the current nav state, compared to the prior
  * month in the nav state. Note that, if there's only
  * one month represented in the nav state, this will return NULL.
  */
DEFINE Input AS 
SELECT
   ARB(DimDate_CalendarYear) AS CalYear,
   ARB(DimDate_MonthNumberOfYear) AS NumMonth,
   DimDate_CalendarYear * 12 + DimDate_MonthNumberOfYear AS OrdinalMonth,
   SUM(FactSales_SalesAmount) AS TotalSales 
FROM SaleState
GROUP BY OrdinalMonth;

RETURN Result AS 
SELECT
   CalYear AS CalYear,
   NumMonth AS NumMonth,
   TotalSales AS TotalSales,
   Input[OrdinalMonth - 1].TotalSales AS PriorMonthSales,
   100 * (TotalSales - PriorMonthSales) / PriorMonthSales AS PercentChange 
FROM Input 
ORDER BY CalYear DESC, NumMonth DESC 
PAGE(0, 1)