How to make attribute work with accumulated values which are not directly included in Calender Hierarchi RRS feed

  • Question

  • Hello Everyone,

    I have a Period dimension with attributes year, quarter ,period and date. And a Hierarchi called Calender = year-quater-period-date

    one of the client requested for another attribute to be included in period dimension called "Report Period". Report period attribute have just 2 members  'Actual period' and 'Closed Period' and all other values are null.  I have createdthis acutal period and closed period in backend database .

    It is infact related to economic transactions every month. For example if the date is 5th november then it will show closed period as september and actual period as october, it is becasue the economic transactions are still open for october month evenon 5th november . But when the transactions are closed for october month on 6th november then the slicer will show 'Actual period' as 201911 and closed period as '201910'. 

    Now coming to the main problem. slicer "Report period" is working with the measures which comes directly from source for example sales, cost etc . But the slicer "Report peirod" is not working with accumulated values such as "Sales Accumualed". Reason for  the attribut "Report Period" is not working with accumulated values is because this attribut is not in the calender Hierarchi.

    Sales Accumulated= sum(YTD([PERIOD].[CALENDER HIERARCHI].currentmember),[Measures].[Sales])

    Is there any way that the attribute "Rapport Period" work with accumulated measures even though it is not included in calender hierachi. Thanks for any suggestions

    Kind Regards


    Friday, November 22, 2019 1:06 PM

All replies

  • Hi mah,

    Per your description, you may use MDX for "From Date" and "To Date" to get expected results.

    Holding History – MDX For “From Date” and “To Date”

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, November 25, 2019 8:21 AM
  • Hello Will,

    Thanks for the link and it is very helpful but unfortunately i cannot change the existing MDX which is running in production . We have found a solution using VBA script, where a hidden excel pivot from same cube  drives the period in the actual excel rapport which users can see. Thanks for the reply.

    Kind Regards


    Monday, December 2, 2019 8:09 AM