none
average balance in year for the selected month RRS feed

  • Question

  • Hi all,

    Tried to write MDX to show average balance for the year for the selected month.

    2009           2 (avg bal)

              01    1

             02      2

             03       3

    existing code...

    measure.[bal]/count(nonempty(date.period.currentmember.children,measures.bal))

    But the above code works when year is selected with all month. But when selecting few months (month 01, 02), the result

    is showing as 1. 

      3/3 =1 (denominator is all children for the year irrespective month selection). Need help how to get correct avg balance when selecting particular month in a year.  when selecting month 02 and 03 the avg balance is 5/2 = 2.5

    tried below calculation as well. still the same result.

    Avg( Descendants([Date].[period].currentmember
    , [Date].[period].[Month])
    , [Measures].[bal]
    )


    Thank you.

    Tuesday, November 12, 2019 11:27 AM

All replies

  • Depending on whether your months selection is represented in the underlying query as WHERE or a subselect, it's either 'count( existing nonempty(', or a dynamic set.

    One other thing to check is whether Date.Period is an unnatural hierarchy.


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, November 12, 2019 12:16 PM
  • Thanks for the hint.

    Got it using Dynamic set.

    Wednesday, November 13, 2019 1:25 AM
  • Thanks for the hint.

    Got it using Dynamic set.

    Since you have got your answer, please kindly close the thread by marking useful reply as answer.

    Thanks for your cooperation.

    Best Regards,

    Will


    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 MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 5:57 AM