Expressions: Derived Cube - PeriodToDateCubeRange

You can do period-to-date calculations using this function. For a banded date dimension, this will return a numeric list comprising the cells from the start of the current period to the current cell. Because a Cube can only return a numeric value, the PeriodToDateCubeRange function cannot be the outermost in the Expression and is used in conjunction with other functions, such as SUM, MEAN, MIN, MAX, to do different calculations.

For example, to calculate year-to-date values:

Note

PeriodToDateCubeRange([cube$0], “MEASURE”, “Dimension”, Number, Periods)

Where:

• “measure” is the name of the measure, or the numeric position of the measure in the list of measures

• “dimension” is the name of the dimension referred to. You cannot refer to the dimension by numeric position in this function. There are some restrictions to the dimension: It must be a date dimension and must be a continuous dimension (i.e. each value is the next date in the calendar). If the dimension fails any of these validations, the result will be a missing value

• “number” will usually be 0 as this means going back to the start of this period. The value of 1 would be to go back to the start of the previous year. The most usual case for a non-zero number would be to say go back to the start of a 6-month period

• “periods” refers to one of the following values - “weeks”, “months”, “quarters”, “years”. A shortcut of the first letter will also work here

 

Click to return to Expressions: Derived Cube