Local Member to calculate Variance between two "Changing" time periods

[Former Member]

Hello experts,

I am having issues with a local member formula to calculate the variance between the current and previous month. I have a report that displays the activity of two months (current and previous) for several accounts in the Column Axis followed by a Local Member that calculates the Variance between the two. However, every time I select two different time periods my Local Member goes away because the formula is anchored to the previously two selected Time Members.

Is there a way to write a different syntax within the Local Member formula box that will calculate the variance accordingly every time the selection changes?

so far I have tried changing the formula in the local member formula box to purely Excel algorithm, but every time I click in the Local member cell within the cell on the report and click Enter the formula goes back to MDX syntax that makes my Local member go away after selecting different period.

Thanks in advance.

[former_member186338 · Accepted solution]

First of all in Sheet Options please disable Local member recognition:

[former_member186338 · Accepted solution]

Second - manually create a local member like here:

PERIODS is my TIME dimension!

[Former Member]

That worked! Thanks Vadim