none
CALCULATE MDX - dividing a measure by the number of days based on 2 different date dimensions in RRS feed

  • 질문

  • Hi,

    I am working on a financial statement dimension that has 3 line items that are supposed to get calculated as a daily average.

    Also have 2 date dimensions, Activity date and Accounting date

    fact data for activity date is at a daily level, but not all days are populated, and granularity for accounting date is monthly.

    There is another dimension that has the account closing period

    The requirement are:

    1. to calculate daily average for both dates, and the correct calculation should show up depending on what is being selected as a group in the excel browser.

    2. to show the correct average at the total level, i.e if the accounting close date is October divide the daily average by days till October of that year.

    I am doing this, but it only works for one of the date dimension and for the total column it divides by 365 everytime:

    SCOPE ([Financial Statement].&[75]);  
    THIS =
    (
                          ([Financial Statement].&[70])/Count(Descendants([Accounting Date].[Date Drilldown], [Accounting Date].[Date Drilldown].[Day]))   

    );   
    END SCOPE;

    Thanks,

    2014년 12월 12일 금요일 오전 3:41

모든 응답

  • Hi,

    Thank you for your question. I am currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    2014년 12월 29일 월요일 오전 8:23
    중재자