How do I correctly use the MDX ClosingPeriod function

Question

We are building a cube for the business who analyse Sales against Stock.

I need a separate measure which will show the Closing Stock Qty.

The business runs in retail periods which contain retail weeks. The week always finishes on a Sunday, so my closing stock quantity needs to be from the Sunday.

So if we are looking at sales against today (Tuesday, 12th July 2016), then the column should show the value for the Sunday just passed, which is Sunday, 10th July 2016.

This needs to be dynamic depending on the level the user is viewing the data at.

If the lowest level is shown at the day level, then for all days for that week, it should show the previous Sunday's value.
If the lowest level is shown at the week level, then each week should show the previous weeks closing balance
Etc

, if the user is not breaking the pivot table down by date, but just retail weeks, then it should show the value for the last day of the previous week. Same rules if the user was breaking the pivot table down to the level of period- it should show the value for the last day of the previous

We have a Date dimension with the following relevant hierarchies:

Calendar
- Calendar Year
- Calendar Quarter
- Calendar Month
- Date
Retail
- Retail Year
- Retail Period
- Retail Week
- Date

I know I can use ClosingPeriod function for this but I am having trouble with the Syntax.

I have the below but am getting NULL's. This is my first real MDX calculation so very new to this!

WITH MEMBER [Measures].[Closing On Hand Qty] AS
(
  ClosingPeriod(
    [Date].[Retail].[Retail Week],
    [Date].[Retail].CurrentMember
  ),
  [Measures].[On Hand Qty]
)

SELECT
  [Date].[Retail].[Date].Members ON ROWS,
  {
    [Measures].[On Hand Qty],
    [Measures].[Closing On Hand Qty]
  } ON COLUMNS
FROM
  Retail
WHERE
  [Date].[Retail Year].&[2017]

whytheq whytheq · Accepted Answer · 2016-07-12T13:00:57

(note: it doesn't throw an exception but the mdx standard is to state columns before rows in the Select clause)

ClosingPeriod I've not played with but something like the following might help:

WITH 
MEMBER [Measures].[Closing On Hand Qty] AS
(
  TAIL(
    DESCENDANTS(
      EXISTING [Date].[Retail].[Retail Week],
      [Date].[Retail].[Date]
    )
  ).ITEM(0).ITEM(0)
 ,[Measures].[On Hand Qty]
)
SELECT
  {
    [Measures].[On Hand Qty],
    [Measures].[Closing On Hand Qty]
  } ON 0,
  [Date].[Retail].[Date].MEMBERS ON 1
FROM Retail
WHERE [Date].[Retail Year].&[2017];

Looking at the docs for ClosingPeriod I think something like the following might work:

WITH MEMBER [Measures].[Closing On Hand Qty] AS
(
  ClosingPeriod(
    [Date].[Retail].[Date],
    EXISTING [Date].[Retail].[Retail Week] 
  ),
  [Measures].[On Hand Qty]
)
SELECT
  {
    [Measures].[On Hand Qty],
    [Measures].[Closing On Hand Qty]
  } ON COLUMNS,
  [Date].[Retail].[Date].Members ON ROWS
FROM Retail
WHERE [Date].[Retail Year].&[2017];

How do I correctly use the MDX ClosingPeriod function

1 Answers