Search code examples
visual-studiossasmdxcube

MDX openingperiod, closingperiod and sum(measure) return the same value

In our cube we want to develop an overview of the amount of goods and their value at the beginning and closing of a month. So I want to use the MDX expressions openingperiod() and closingperiod(), they should provide these metrics.

In Visual Studio we have the following code (only for openingperiod() since closingperiod() has the same syntax)

(OPENINGPERIOD
(
[Date Booking].[Month].[Month],
[Date Booking].[Month].currentmember
), 
[Measures].[AT Amount])

The results for openingperiod() and closingpeiod() are the same as well as for the measure [AT Amount] = sum(Amount)

I expected three different outcomes, as documentations and examples show. However all three outcomes are the same.

Consulted pages:

https://learn.microsoft.com/en-us/sql/mdx/openingperiod-mdx?view=sql-server-2017

https://www.sqlservercentral.com/steps/stairway-to-mdx-level-12-mdx-timedate-series-functions-openingperiod-and-closingperiod-functions

Solution

  • Comparing your code against the example, I suspect you are trying to use this technique against hierarchy with a single level.

    The example was for a multi-level hierarchy Year / Semester / Quarter / Month / Date. In that scenario, when using the Month level to slicer your measure, the CURRENTMEMBER property returns each Month member, which is applied to return first the child member at the Date level (as specified in the 1st parameter of OPENINGPERIOD).

    With your code, the CURRENTMEMBER property returns each Month member, which is applied at the Month level, so it will return the same Month member for OPENINGPERIOD or CLOSINGPERIOD (or effectively use the same Month member for SUM).

    I would construct a Calendar hierarchy with Date level below a Month level (other levels are optional), then I would edit your code to:

    (OPENINGPERIOD
(
[Date Booking].[Calendar].[Date],
[Date Booking].[Calendar].currentmember
), 
[Measures].[AT Amount])