locked
Revenue spread evenly across months RRS feed

  • Question

  • Hello everyone

    I'm pretty new with MDX and tried to search the web for a solution for my problem, but couldn't sadly find one. I have a Calendar Hierarchy, in my columns, with 3 levels which are year, month and date. In my Rows i have several dimensions in the form of customer, product, account etc. My measure is revenue. 

    The problem i'm facing is that when i choose the [Calendar].[year] im getting a result for 2017 of say 120 Dollars, which by itself is correct, but when i then try to make an evenly monthly allocation across 12 months, by choosing [Calender.Month] it automatically take the actual revenue say Jan=12, Feb=9, Mar=13. I want it to evenly distribute the 120 across the 12 months so that jan=10, Feb=10, Mar=10 etc. 

    Hope someome can help with this 

    Wednesday, November 15, 2017 8:23 AM

Answers

  • Hi Aagaard806,

    Thanks for your question.

    I would not suggest you to do so. But if you do want this, I would suggest you to create a another copy measure which is identical to Measure revenue as below in calculation script TAB of your cube design window:

    CREATE MEMBER CURRENTCUBE.[Measures].[Copy Revenue] AS
    [Measures].[Revenue];

    Then you can overwrite the cells with the value you want by using SCOPE statement as below:

    SCOPE([Measures].[Copy Revenue] );
    SCOPE([Date].[Calendar].[Month]);
    THIS = ([Date].[Calendar].CurrentMember.Parent,
            [Measures].[Revenue])/12 ;
    END SCOPE;
    END SCOPE;


    Best Regards
    Willson Yuan
    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 15, 2017 9:07 AM
  • Hi Aagaard806,

    Thanks for your response.

    In this scenario,Please replace [Calendar].[Y -Q - M].CurrentMember.Parent  with [Calendar].[Y -Q - M].CurrentMember.Parent.Parent.

    See below sample code:

    CREATE MEMBER 
     CURRENTCUBE.[Measures].[Evenly weighted] AS 
     [Measures].[Value Local];
    
     SCOPE ([Measures].[Evenly weighted]);
     SCOPE ([Calendar].[Y -Q - M].[Calendar month]);
     THIS = ([Calendar].[Y -Q - M].CurrentMember.Parent.Parent,
    [Measures].[Evenly weighted])/12;
     END SCOPE;
     END SCOPE;

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues, your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    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

    • Marked as answer by Aagaard806 Friday, November 24, 2017 11:59 AM
    Thursday, November 23, 2017 6:18 AM

All replies

  • Hi,

    You can add a calculated member like this :

    WITH MEMBER [RevenueByMonth] AS  
    ([DATES].[Year].CurrentMember,[Measures].[Revenue])/12
    
    SELECT ([DATES].[Year -  Month -  Date].MEMBERS, [RevenueByMonth]) ON COLUMNS,
    [Product].[Product].Members ON ROWS
    FROM [Cube]
    WHERE ([DATES].[Year].&[2017-01-01T00:00:00])


    Please mark as answered, If you feel happy with this answer.

    Wednesday, November 15, 2017 8:58 AM
  • Hi Aagaard806,

    Thanks for your question.

    I would not suggest you to do so. But if you do want this, I would suggest you to create a another copy measure which is identical to Measure revenue as below in calculation script TAB of your cube design window:

    CREATE MEMBER CURRENTCUBE.[Measures].[Copy Revenue] AS
    [Measures].[Revenue];

    Then you can overwrite the cells with the value you want by using SCOPE statement as below:

    SCOPE([Measures].[Copy Revenue] );
    SCOPE([Date].[Calendar].[Month]);
    THIS = ([Date].[Calendar].CurrentMember.Parent,
            [Measures].[Revenue])/12 ;
    END SCOPE;
    END SCOPE;


    Best Regards
    Willson Yuan
    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 15, 2017 9:07 AM
  • Hi,

    why did you use a dimension as a measure? [Account].[Account external].[Evenly weighted] is a dimension member, not a measure. Please follow wilson's reply,replace [Account].[Account external].[Evenly weighted] with [Measures].[Revenue].


    • Edited by alexander fun Wednesday, November 15, 2017 1:48 PM edit
    Wednesday, November 15, 2017 1:43 PM
  • Hello again 

    Thank you for your patience :) I was a bit confused of what my revenue measure was because of the structure within our cube, but i found out that the following code provides an output 

    CREATE MEMBER 
    CURRENTCUBE.[Measures].[Evenly weighted] AS -- Copy of revenue measure 
    [Measures].[Value Local];

    SCOPE ([Measures].[Evenly weighted]);
    SCOPE ([Calendar].[Y -Q - M].[Calendar month]);
    THIS = ([Calendar].[Y -Q - M].CurrentMember.Parent,
    [Measures].[Evenly weighted])/12;
    END SCOPE;
    END SCOPE;

    The code allmost works as its supposed to. The problem is that i have a folder for each year, Example 2011,2012,2013 etc. within each of these folders there are 4 folders for 1.Quarter 2.Quarter, 3. Quarter 4. Quarter. When i enter one of these folders i have 1.Quarter --> Jan, Feb, Mar. 2.Quarter --> Apr, May, Jun

    The problem with this is that, the above code, takes the Quarters as the parent, hence it devides each quarter by 12 instead of the year by 12. To illustrate this say 2015 revenue is 120 and the following distribution across quarters 1.Q=20, 2.Q=40,3.Q=50, 4.Q=10. This gives me the following output. Jan=20/12=1,66, Feb=1,66, Mar=1,66, Apr=40/12=3,33, may=3,33, Jun=3,33, Jul=50/12=4,16 etc, where the correct should be jan=10, Feb=10, Mar=10 etc.

    Is there any way to correct this? 

    Again thank you so much for your assistance 

    Wednesday, November 22, 2017 10:44 AM
  • Hi Aagaard806,

    Thanks for your response.

    In this scenario,Please replace [Calendar].[Y -Q - M].CurrentMember.Parent  with [Calendar].[Y -Q - M].CurrentMember.Parent.Parent.

    See below sample code:

    CREATE MEMBER 
     CURRENTCUBE.[Measures].[Evenly weighted] AS 
     [Measures].[Value Local];
    
     SCOPE ([Measures].[Evenly weighted]);
     SCOPE ([Calendar].[Y -Q - M].[Calendar month]);
     THIS = ([Calendar].[Y -Q - M].CurrentMember.Parent.Parent,
    [Measures].[Evenly weighted])/12;
     END SCOPE;
     END SCOPE;

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues, your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    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

    • Marked as answer by Aagaard806 Friday, November 24, 2017 11:59 AM
    Thursday, November 23, 2017 6:18 AM