The following example finds the most recent month in the data that matches the current filters, and compares it to the prior month, again in the data that matches the current filters.
/* 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)