For an example, I am utilizing the following formula as a statistical account with 103 being the key for
“Accounts Receivable” which allows my cashflow to switch between YTD and MTD when time perspective is a page.
LAG([103],1)-[103]
I would like some help modifying this formula so the “Lag” always uses the “Actual” version while the second amount in the formula will utilize whatever is set as default in version. Example, if the Budget Version is selected on the page, this formula would calculate actual minus budget or actual minus whatever version is selected.
In your case, as you have realized, you would need to convert your formula to MDX and then add a version component to the syntax.
Steps
Click on the ‘Define formula using MDX’ on the formula section
This should convert your formula to MDX, something like the below
([Account].[Default].&[103],[Time].[Default].CurrentMember.LAG( 1))-[Account].[Default].&[103]
Add the version part i.e. ,[Version].[Default].&[ACT] to the first part of your formula
Your formula should look like this at the end of this exercise
([Account].[Default].&[103],[Time].[Default].CurrentMember.LAG( 1),[Version].[Default].&[ACT])-[Account].[Default].&[103]
This will ensure that the Lag value will always point to ACT for reference while the value for the current time (i.e. the second part of your formula after the minus sign), will be in the current version that’s selected.