locked
YTD ending in the Current Period RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I'm using YTD to get the value from the intial period (month, quarter, day) to the current period. The problem is because there are month that are displaying values when the real values are 0(zero). For example:

    Real Values

    ----------

    Jan  500

    Feb  0

    Mar  0

    Apr  0

    The YTD values displayed are the following:

    YTD

    ----------

    Jan 500

    Feb  500

    Mar  500

    .....

    Dec  500

    Show the same value for all the months.

     

    And for the last year, if in some case there is one month with 0 (zero) value, i want to display the YTD. For example:

    Real Values

    ----------

    Jan  200

    Feb  300

    Mar  0

    Apr  350

    So, the YTD should be:

    YTD

    ----

    Jan  200

    Feb  500

    Mar  500

    Apr  850

    Therefore, i cannot use always ytd() function. How can i solve this problem?
    Thanks in advance.
    Monday, January 3, 2011 11:38 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi George,

    The YTD function should work just fine in the second case as well:

    YTD([Date].[Calendar Year].CurrentMember) --the set of all members from the first to the current

    AGGREGATE(YTD([Date].[Calendar Year].CurrentMember),[Measures].[Your Measure]) --the aggregate of all values up to the current one

    In example:

    WITH
    MEMBER [Measures].[YTD ISA]
        AGGREGATE(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])
    SELECT
    {
        [Measures].[Internet Sales Amount],
        [Measures].[YTD ISA]
    } ON 0,
    {
        [Date].[Calendar].[Month]
    } ON 1
    FROM [Adventure Works]

    Whether the months have 0 or null against them should not change anything.

     

    Boyan Penev --- http://www.bp-msbi.com
    • Proposed as answer by Jerry Nee Wednesday, January 5, 2011 10:57 AM
    • Marked as answer by Jerry Nee Tuesday, January 11, 2011 9:44 AM
    Tuesday, January 4, 2011 1:03 AM