Document toolboxDocument toolbox

PreviousPeriods

Returns a set of previous time dimension members for calculation of cumulative sums.

Syntax

PreviousPeriods(Member_Expression)

Arguments

Member_ExpressionMDX expression that returns a member.

Examples

PreviousPeriods returns optimized set of previous periods that can be used to aggregate some measure value for all previous time periods.

For example, PreviousPeriods( [Time].[2015].[Q2 2015].[Jun 2015].[Jun 03 2015] ) will return a set:

{
  [Time].[2014],
  [Time].[2015].[Q1 2015],
  [Time].[2015].[Q2 2015].[Apr 2015], [Time].[2015].[Q2 2015].[May 2015],
  [Time].[2015].[Q2 2015].[Jun 2015].[Jun 01 2015], [Time].[2015].[Q2 2015].[Jun 2015].[Jun 02 2015]
}

which is all years until previous year (in this example Time dimension starts with year 2014), all current year quarters until previous quarter (in this example Q1 2015), all current quarter months until the previous month (Apr 2015 and May 2015) and all current month days until the previous day (Jun 01 2015 and Jun 02 2015).

The following formula can be used to calculate a cumulative sum of invoice count until the previous period:

Sum(
  PreviousPeriods([Time].CurrentHierarchyMember),
  [Measures].[Invoice count]
)

If we want to calculate a cumulative sum of invoice count including the current period then we need to add the current period to the previous periods set:

Sum(
  { PreviousPeriods([Time].CurrentHierarchyMember),
   [Time].CurrentHierarchyMember },
  [Measures].[Invoice count]
)