none
Counting Time dimension members having data

    Question

  • I have a time dimension structured as Year > Quarter > Month. Dimension name is [Time]. Tree Name is [Time Tree]

    I have a measure called [Revenue USD].

    I am making a report for a Quarter wherein I have the Revenue reported as 

    Q1 : $300

    Q2 : $200

    Now I need to show how many months comprise this revenue. For example, in above reporting, Q1 revenue is for 3 months while Q2 is only for 2 months

    Q1 : $300 : 3 months

    Q2 : $200 : 2 months

    How do I get a count of months, where the revenue > 0?

    Monday, June 24, 2013 10:02 AM

Answers

  • Hello Anisha,

       If you are having a hierachy , please try similar like below adventure works sample MDX

     

    WITH MEMBER MEASURES.NonemptyMonthCount
    as
    COUNT(NONEMPTY([Date].[Calendar].CURRENTMEMBER.CHILDREN,[Measures].[Order Count] ))
    SELECT NONEMPTY([Date].[Calendar].[Calendar Quarter] ,[Measures].[Order Count]) ON 1
    	,{[Measures].[Order Count],MEASURES.NonemptyMonthCount} ON 0
      FROM [Adventure Works]
      WHERE 
      (
      [Date].[Calendar Year].&[2005]
      )


    Best Regards Sorna

    Monday, June 24, 2013 1:49 PM