Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2011-03-16
Medium Priority
387 Views
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
Question by:obtek

LVL 21

Accepted Solution

Jason Yousef, MS earned 1000 total points
ID: 35156434
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

LVL 9

Assisted Solution

Hwkranger earned 1000 total points
ID: 35307482
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
###### Suggested Courses
Course of the Month21 days, 1 hour left to enroll