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 some 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 the current date, type the minus sign and then click on a measure cross-joined with the previous date, as illustrated below:

Writing an MDX Query

This option is extremely useful when you need 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 example below, it is important that the 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 do not contain the required previous date, the previous date might evaluate to nothing, meaning that the result is not 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 this instance, 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 no longer compute the difference:

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 this instance, for the difference to work as expected, access needs to be granted 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 values 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.

From ActivePivot 5.6 onwards, 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.