none
Limit Revenue from July to Dec(Show only Jan to June) for every FY year when any date is Passed Dynamically

    Question

  • Hi

    I need to restrict revenue if I pass any date from  July to dec, that means set to '0' , in actual there is revenue but mask it with $0,

    if we pass any date from  Jan to June then render revenue

    Eg:

    FY 14

    July 100,Aug :0,Sept:10,Oct:50,Nov:5,Dec:0,Jan:0,Feb:10,Mar:50,Apr:10,may:20,Jun:0

    FY15

    July 100,Aug :5,Sept:NULL,Oct:NULL,Nov:NULL,Dec:NULL,Jan:NULL,Feb:NULL,Mar:NULL,Apr:NULL,may:NULL,Jun:NULL

    If I run MDX By passing date Parameter todays date result set would be '0' since we need mask with  $0

    Result

    FY15    Fy14

    0          0

    Assuming if am at Feb 2015 If I run MDX By pass date Parameter 02/28/2015  result set would be

    data Set up

    FY 14

    July 100,Aug :0,Sept:10,Oct:50,Nov:5,Dec:0,Jan:0,Feb:10,Mar:50,Apr:10,may:20,Jun:0

    FY15

    July 100,Aug :5,Sept:0,Oct:10,Nov:0,Dec:20,Jan:10,Feb:80,Mar:NULL,Apr:NULL,may:NULL,Jun:NULL

    If I run MDX for date 02/28/2015 my range for Current year is June 01 2014 to 02/28/2015 and Previous year is

    June 01 2013 to 02/28/2014

    Here expected result always consider from Jan2015 to June2015 revenue by masking July2014 to dec2014 to "$0"

    Result

    FY15    Fy14

    90         10

    MDX Code

    it is running fine I pass date for 02/28/2015 it give me correct results But if I ran for today date  it is not giving correct results

    WITH
      MEMBER [MEASURES].[CurrentFYYTD] AS
        Sum
        (
          {
              Ancestor
              (
                [Date].[FY Year - Quarter - Month - Date].CurrentMember
               ,[Date].[FY Year - Quarter - Month - Date].[FY Year]
              ).FirstChild.FirstChild.Lead(6).FirstChild
            :
              [Date].[FY Year - Quarter - Month - Date].CurrentMember
          }
         ,[Measures].[Revenue]
        )
      MEMBER [MEASURES].[PreviousFYYTD] AS
         Sum
        (
          {
              Ancestor
              (
                [Date].[FY Year - Quarter - Month - Date].CurrentMember
               ,[Date].[FY Year - Quarter - Month - Date].[FY Year]
              ).Lag(1).FirstChild.FirstChild.Lead(6).FirstChild
            :
              Cousin
              (
                [Date].[FY Year - Quarter - Month - Date].CurrentMember
               ,Ancestor
                (
                  [Date].[FY Year - Quarter - Month - Date].CurrentMember
                 ,[Date].[FY Year - Quarter - Month - Date].[FY Year]
                ).Lag(1)
              )
          }
         ,[Measures].[Revenue]
        )

      MEMBER [Measures].[EmployeeID Caption] AS
        [Employees].[Manager Employee].CurrentMember.Member_Key
    SELECT
      {
        [Measures].[EmployeeID Caption]
       ,[MEASURES].[CurrentFYYTD]
       ,[MEASURES].[PreviousFYYTD]
      } ON 0
     ,NON EMPTY
        {Descendants([Employees].[Manager Employee].&[2])} ON ROWS
    FROM Accounting
    WHERE
      [Date].[FY Year - Quarter - Month - Date].&[20140807];

    Any modification for above mdx to get right results would be great!

    Thanks.



    ProM


    • Edited by Promethues Monday, August 11, 2014 4:49 PM
    Monday, August 11, 2014 4:38 PM

All replies

  • Hi Prom,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, August 13, 2014 3:21 AM
    Moderator
  • Hi Prom,

    From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Regards

    Monday, August 18, 2014 7:44 AM