Day-to-day difference

Day to day difference can be implemented using the Formula Editor or using MDX.

Using Formula editor

This is a quick way to calculate a difference for a specific measure.

It does not require any knowledge of MDX. However, if you would like the formula to roll forward every day, you need to write a custom MDX as defined in the next chapter.

1. Add desired measure into the View
2. Add dates on columns (it is convenient to click them from the Formula Editor)
3. Click "+" to add a measure and switch to the Formula editor
4. Click on one measure cross-joined with current date, type the minus sign and click on measure cross-joined with previous date, as illustrated below:

Writing MDX Query

This option serves well when you would like to calculate differences for many measures at once - you can create a member computing the difference in the date dimension (see "with" expression below).

Keywords CurrentMember and CurrentMember.NextMember refer to two consecutive dates in Time dimension.

Note: in the below example, it is important that scope contains two consecutive dates.

WITH
 Member [Time].[HistoricalDates].[Change] AS [Time].[HistoricalDates].CurrentMember - [Time].[HistoricalDates].CurrentMember.NextMember
SELECT
  NON EMPTY {
    [Measures].[vega.SUM],
    [Measures].[gamma.SUM],
    [Measures].[delta.SUM]
  } ON ROWS,
  NON EMPTY {
    [Time].[HistoricalDates].CurrentMember,
    [Time].[HistoricalDates].CurrentMember.NextMember,
    [Time].[HistoricalDates].[Change]
  } ON COLUMNS
  FROM [EquityDerivativesCube]

Important note

The evaluation of the formula expression takes into account all filters. If the filters does not contain the required previous date, the previous date might evaluate to nothing and the result may not be as intended.

Consider the following formula:

WITH
 Member [Measures].[Pnl Diff] AS (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
  [Measures].[pnl.SUM]
) - (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-07],
  [Measures].[pnl.SUM]
), FORMAT_STRING = "#,###.##"

As dates are often defined as slicing hierarchies in the cube, a filter on dates is implicitly defined as a slicer. One can also explicitly define a date filter by selecting a single date. In such a case, it will appear as a slicer and the result will be as expected:

WITH
 Member [Measures].[Pnl Diff] AS (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
  [Measures].[pnl.SUM]
) - (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-07],
  [Measures].[pnl.SUM]
), FORMAT_STRING = "#,###.##"
SELECT
  NON EMPTY {
    [Measures].[Pnl Diff],
    [Measures].[pnl.SUM]
  } ON COLUMNS,
  NON EMPTY Hierarchize(
    DrilldownLevel(
      [Booking].[Desk].[ALL].[AllMember]
    )
  ) ON ROWS
  FROM [EquityDerivativesCube]
  WHERE [Time].[HistoricalDates].[AsOfDate].[2018-05-08]

If however the same filter is defined as a subselect, the evaluation will not compute the difference anymore:

WITH
 Member [Measures].[Pnl Diff] AS (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
  [Measures].[pnl.SUM]
) - (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-07],
  [Measures].[pnl.SUM]
), FORMAT_STRING = "#,###.##"
SELECT
  NON EMPTY {
    [Measures].[Pnl Diff],
    [Measures].[pnl.SUM]
  } ON COLUMNS,
  NON EMPTY Hierarchize(
    DrilldownLevel(
      [Booking].[Desk].[ALL].[AllMember]
    )
  ) ON ROWS
  FROM (
    SELECT
    [Time].[HistoricalDates].[AsOfDate].[2018-05-08] ON COLUMNS
    FROM [EquityDerivativesCube]
  )

In such a case, for the difference to work as expected, one need to grant access to both dates:

WITH
 Member [Measures].[Pnl Diff] AS (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
  [Measures].[pnl.SUM]
) - (
  [Time].[HistoricalDates].[AsOfDate].[2018-05-07],
  [Measures].[pnl.SUM]
), FORMAT_STRING = "#,###.##"
SELECT
  NON EMPTY {
    [Measures].[Pnl Diff],
    [Measures].[pnl.SUM]
  } ON COLUMNS,
  NON EMPTY Hierarchize(
    DrilldownLevel(
      [Booking].[Desk].[ALL].[AllMember]
    )
  ) ON ROWS
  FROM (
    SELECT
    {
      [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
      [Time].[HistoricalDates].[AsOfDate].[2018-05-07]
    } ON COLUMNS
    FROM [EquityDerivativesCube]
  )

To help you handle those subtleties, consider using iif construction to return an error if one of the date value is not defined.

Example:

WITH
 Member [Measures].[Pnl Diff] AS iif(
  IsEmpty(
    [Time].[HistoricalDates].[AsOfDate].[2018-05-07]
  ) or isEmpty(
    [Time].[HistoricalDates].[AsOfDate].[2018-05-08]
  ),
  "Please include both 2018-05-07 and 2018-05-08 in your filters",
  (
    [Time].[HistoricalDates].[AsOfDate].[2018-05-08],
    [Measures].[pnl.SUM]
  ) - (
    [Time].[HistoricalDates].[AsOfDate].[2018-05-07],
    [Measures].[pnl.SUM]
  )
), FORMAT_STRING = "#,###.##"

This problem can be avoided when the difference is implemented as a post-processor. In such a case, users can still have control over the post-processor by leveraging the ActiveMeasure concept.

Also, since ActivePivot 5.6, it is possible to define your measure in such a way that the difference will correctly compute the result contrary to the example given above. Ask your product development team for more information.