locked
Formula based on today's date RRS feed

  • Question

  • Hi,

     

    I am not sure this is achievable but I would like a Calculated Member to work based on on today's date.

    Basically the general logic is;

    If today is not a selling day then use formula a if not, use formula b.

    Time Hierarchy [Time].[Time By Month] is [Year][Quarter][Month][Date]

    Each Date has an attribute being [Current Day] which can be 1 or 0 (1 meaning its today) and [Selling Day] which can be 1 or 0 (1 meaning its a selling day).

    Is this possible?

    Thanks,

    B.

    Thursday, June 16, 2011 12:28 PM

Answers

  • You can build your calculation in the following way;

    WITH 
      MEMBER Measures.x AS 
        IIF
        (
            StrToMember
            ("[Date].[Calendar].[Date].&["
                + VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")
              + "]"
            ).Properties('Selling Day')
          = 1
         ,a
         ,b
        ) 
    SELECT 
      {Measures.x} ON 0
    FROM [Adventure Works];

    In the above example we are comparing the Selling day value for today and then assigning formula a or b.

    Modify calculation as per your requirement and Date key.


    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by BSaliba Friday, June 17, 2011 1:35 PM
    Friday, June 17, 2011 6:16 AM
  •  

    Was trying something on the lines of;

    iif (
    ([Time].[Time By Month].[date],[Time].[Current Date].&[1],[Time].[Selling Day].&[0]).Item(0) = strToMember("[Time].[Time By Month].[Date].&[" + str(year(Now())) + "-" + format((month(Now())), "00") + "-"  + format((day(Now())), "00") + "T00:00:00" + "]"),
    ([Measures].[Act Sales] / ([Elapsed Selling Days]+1)) * [Selling Days],
    ([Measures].[Act Sales] / [Elapsed Selling Days]) * [Selling Days]) 

     

    I have edited the post, this final version seems to work. Will need to test it further but will leave it here for reference.

    Feel free to suggest any improvements.

     

    Thanks

    B.

     




    • Edited by BSaliba Thursday, June 16, 2011 1:31 PM Changed the formula
    • Marked as answer by BSaliba Friday, June 17, 2011 1:35 PM
    Thursday, June 16, 2011 1:06 PM

All replies

  •  

    Was trying something on the lines of;

    iif (
    ([Time].[Time By Month].[date],[Time].[Current Date].&[1],[Time].[Selling Day].&[0]).Item(0) = strToMember("[Time].[Time By Month].[Date].&[" + str(year(Now())) + "-" + format((month(Now())), "00") + "-"  + format((day(Now())), "00") + "T00:00:00" + "]"),
    ([Measures].[Act Sales] / ([Elapsed Selling Days]+1)) * [Selling Days],
    ([Measures].[Act Sales] / [Elapsed Selling Days]) * [Selling Days]) 

     

    I have edited the post, this final version seems to work. Will need to test it further but will leave it here for reference.

    Feel free to suggest any improvements.

     

    Thanks

    B.

     




    • Edited by BSaliba Thursday, June 16, 2011 1:31 PM Changed the formula
    • Marked as answer by BSaliba Friday, June 17, 2011 1:35 PM
    Thursday, June 16, 2011 1:06 PM
  • If you are having two attributes with [CurrentDay] and [SellingDay] then why don't  you simply compare the CurrentDay and SellingDay using properties.

    http://msdn.microsoft.com/en-us/library/ms144821.aspx


    Aniruddha http://aniruddhathengadi.blogspot.com/
    Thursday, June 16, 2011 3:02 PM
  • Thanks for your reply but how would you apply that to the above?

    Regards,

    B.

     

     

     

     

    Thursday, June 16, 2011 3:08 PM
  • You can build your calculation in the following way;

    WITH 
      MEMBER Measures.x AS 
        IIF
        (
            StrToMember
            ("[Date].[Calendar].[Date].&["
                + VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")
              + "]"
            ).Properties('Selling Day')
          = 1
         ,a
         ,b
        ) 
    SELECT 
      {Measures.x} ON 0
    FROM [Adventure Works];

    In the above example we are comparing the Selling day value for today and then assigning formula a or b.

    Modify calculation as per your requirement and Date key.


    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by BSaliba Friday, June 17, 2011 1:35 PM
    Friday, June 17, 2011 6:16 AM
  • Thanks Aniruddha,

     

    Thats a neat solution.

     

    B.

    Friday, June 17, 2011 1:35 PM