Get the first of the month for a given date in MDX as well as compound interest
Posted on 2011-03-16
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).