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

# 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
2 Solutions

Sr. 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

Commented:
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]
,1)
.item(0)
,[Measures].[Active Relationship])
-
,1)
.item(0)
,[Measures].[New Relationship]),
FORMAT_STRING = "#,#;(#,#);0;\-",
VISIBLE = 1;
``````
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.