MDX Claculation - Solved

[phantomluvs]

Creating Balance Sheet in Report studio with TM1 as source.

Got stuck with calculation of  total retained earnings (formula is  + Beginning retained earnings + Net income during the period - Dividends paid = Ending retained earnings)

In My case Expected Output is - Total Retained Earnings: (134,200,301) + 18,865,076 = (115,335,225)
Retained earnings + Net Income Loss of Jul 15 (is the closed month) + YTD of the same month

Any help / Ideas are appriciated

Attached are the screenshots

[Francis aka khayman]

a lot more information is needed. Is your Curr Mnth YTD = Jul'15?

what is your actual calculation for Retained Earning?

[phantomluvs]

Hi Khayman,
First thank you.

Yes the YTD is a calc based on the prompt selected from the time hierarchy.
Calc for retained earnings is a sum of these two members [30320 - Retained Earning] + [NI - Net Income/(Loss)]. The caveat is the [30320 - Retained Earning] is a monthly value and [NI - Net Income/(Loss)] should be YTD of selected month in prompt.

I also attached a spreadsheet with out and expected output formula.

Let me know if this helps.

My Filter Calculations which are driving the columns:
•   For Month: [Monthly Income Statement].[t_time].[t_time].[Month]->?Month?
•   For YTD: aggregate ( currentMeasure within set (periodsToDate ([Monthly Income Statement].[t_time].[t_time].[Year], parallelPeriod ([Monthly Income Statement].[t_time].[t_time].[Month],0,[Monthly Income Statement].[t_time].[t_time].[Month]->?Month?))))

[phantomluvs]

Solved!!!!

This was a little too much of thinking (silly me).

I solved it by not thinking so much. I sub-nested the YTD aggregated value to rows and converted it to member and used tuple function to do the math,  as simple as that.