Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Get the first of the month for a given date in MDX as well as compound interest

How can I get the first non empty day from the calendar dimension.

Actually I am trying to implement a compount interest calculation


I can do that in two ways.  sum of total profit till date of month /principal as of first of date

I am not able to get the principal as of first date. I greatly appreciate is somebod has a code for it. I tried Opening Period but of no success. Getting Nulls at day level. Works at month level

the second way is

CREATE MEMBER CURRENTCUBE.[Measures].CUM_PNL_PCT
 AS ((1 + ([TradeDate].[TDHierarchy].PrevMember,Measures. [CUM_PNL_PCT])) * (1 + ([TradeDate].[TDHierarchy].CurrentMember, [Measures].[PNL Pct By Aum]))) - 1

The calc works if there are only time dimensions (Day on y axis and year and month of x axis).

If I have any other dimension it is failing.  Also I need to reset at any boundary (like new year).

Thanks.



0
obtek
Asked:
obtek
2 Solutions
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Hello,
just to let you get started...

The first day of the month is using the "OpenningPeriod"  function,

also check that it might help:
http://www.datawarehousingguide.com/microsoft-bi-interview-questions/26-mdx-interview-questions-time-based-function-.html


also these cheeats can help:
http://www.ssas-info.com/ssas-cheat-sheets


sorry can't help with the compound interest, hope someone else answers that.

Thanks
Jason
0
 
HwkrangerCommented:
To get the first day of a reporting period i use HEAD.

i've included an example that takes two metrics at the first day of the month and does a calculation between them.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Open@Beg]
 AS (HEAD(EXISTING [Date].[Calendar].[Date]
            ,1)
            .item(0)
            ,[Measures].[Active Relationship])
-
(HEAD(EXISTING [Date].[Calendar].[Date]
            ,1)
            .item(0)
            ,[Measures].[New Relationship]), 
FORMAT_STRING = "#,#;(#,#);0;\-", 
VISIBLE = 1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now