MDX Formula Help

­­General Discussions
1

I am trying to calculate the Gross Profit %, the formula works good for time perspective base, however when I choose TTM it gives weird results. So i decided to create a MDX which calculates the Margin based on whether it is base or TTM, but it is not working. Can anyone advise on the syntax.

IIF([Time Perspective].[Default].CurrentMember=[Base]
,([Account_Subaccount].[Default].&[GP]/[Account_Subaccount].[Default].&[4000])

,SUM(lastperiods(12,[Time].default.currentmember),[Account_Subaccount].Default.&[GP])
/
SUM(lastperiods(12,[Time].default.currentmember),[Account_Subaccount].Default.&[4000])
)

2

Hi Chaya,

The formula to create a TTM value for GP % (using the keys in your post) would be

(
(SUM(lastperiods(12,[Time].[Default].Currentmember),([Account_Subaccount].[Default].&[GP],[Time Perspective].[Default].&[BASE])))
/
(SUM(lastperiods(12,[Time].[Default].Currentmember),([Account_Subaccount].[Default].&[4000],[Time Perspective].[Default].&[BASE])))
)

If you are creating the formula in the accounts dimension, you may need to have 2 GP % accounts,
one for Base (using the first part of your formula), and
one for TTM (using the above formula).

FYI, the IF condition syntax for Time Perspective is
IIF([Time Perspective].[Default].CurrentMember IS [Time Perspective].[Default].&[BASE]

Feel free to let me know if you have any further questions.

Hope this helps,
Navin

2 Likes
3

Thaks, Naveen I tried tis but looks like something is wrong.

image

4

Sorry Naveen , I take that back, it worked after I also used Formula in Time Conversion. Thanks for your help.

1 Like
5

Great to hear that you got it to work, @chaya.khannukar.

Would you be able to share the formulas you set for the Time Perspective and Accounts? Would love to know what / how you got it to work.

Cheers,
Navin