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

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

/* 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
   DimDate_CalendarYear AS "Year",
   DimDate_MonthNumberOfYear AS "Month",
   DimDate_CalendarYear * 12 + DimDate_MonthNumberOfYear AS OrdinalMonth,
   SUM(FactSales_SalesAmount) AS TotalSales GROUP BY OrdinalMonth;

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