none
Calculated measure doesn't slice properly

    Question

  • Hi there,

    I have a revenue cube has MTD net revenue measure, and I created calculated measure, YTD net revenue:

     Sum(PeriodsToDate(

    [Date DIM].[Fiscal Date].[Fiscal Year],

    [Date DIM].[Fiscal Date].CurrentMember

    ),

    ([Measure].[MTD net revenue])

    )

     

    [Measure].[MTD net revenue] can be sliced by Date, Region, Office, Department but not YTD net revenue.

    Our fiscal year starts with Jul, for example, MTD net revenue 

    Month    Region Office   Department   MTD net revenue

    Jul 2010, GTA, Toronto, DepartmentA 1111.00

    Jul 2010 GTA Toronto DepartmentB 2222.00

    Aug 2010 GTA Toronto DepartmentA 3333.00

    but no MTD net revenue for Department B

    When slicing YTD net revenue, everything looks good in Region office level

    Month    Region Office   YTD net revenue

    Jul 2010 GTA    Toronto  3333.00

    Aug 2010 GTA  Toronto  6666.00

    But it's not correct in Department level:

    Month    Region Office   Department  YTD net revenue

    Jul 2010 GTA    Toronto DepartmentA 1111.00

    Jul 2010 GTA    Toronto DepartmentB  2222.00

    Aug 2010 GTA  Toronto DepartmentA  4444.00

    YTD net revenue for departmentB is missing which should appear as:

    Aug 2010 GTA Toronto DepartmentB 2222.00

    Is it because there is no MTD net revenue in Aug 2010 for DepartmentB? What can be done to avoid this?

    Any suggestion would be appreciated.

     

     

     

    Wednesday, December 1, 2010 5:01 AM

Answers

  • ".. Is it because there is no MTD net revenue in Aug 2010 for DepartmentB? What can be done to avoid this? .." - is there a NON_EMPTY_BEHAVIOR clause included in the MDX script for [Measure].[YTD net revenue], like:

    NON_EMPTY_BEHAVIOR = {[Measure].[MTD net revenue]}

    That would be incorrect and could cause this issue.


    - Deepak
    Wednesday, December 1, 2010 2:58 PM
    Moderator

All replies

  • ".. Is it because there is no MTD net revenue in Aug 2010 for DepartmentB? What can be done to avoid this? .." - is there a NON_EMPTY_BEHAVIOR clause included in the MDX script for [Measure].[YTD net revenue], like:

    NON_EMPTY_BEHAVIOR = {[Measure].[MTD net revenue]}

    That would be incorrect and could cause this issue.


    - Deepak
    Wednesday, December 1, 2010 2:58 PM
    Moderator
  • Thanks Deepak.

    I removed NON_EMPTY_BEHAVIOR, it sliced correctly in the SSMS, but caused MDX query hanged in the report. We are still investigating the reason.

    Thursday, December 2, 2010 5:48 PM
  • ".. but caused MDX query hanged in the report .." - so you may have a performance problem to investigate. In the report, is [Date DIM].[Fiscal Date] at the month level, as your example showed? In that case, YTD net revenue should only be summing at most 12 members?
    - Deepak
    Thursday, December 2, 2010 8:18 PM
    Moderator
  • It's another problem. Your solution works great. Thanks.
    Wednesday, December 8, 2010 8:28 PM