Set analysis in script

[jelindbe]

I try to load a variable defined as:

Set vMyformula="Sum({<Year={$(=MAX({$<[Sales] -= {'=(NULL())'} >}Year))}>}[Sales])";

When I call this variable with $(vMyformula) in a table it returns  Sum({<Year={}>}[Sales]). The expected result would be Sum({<Year={2020}>}[Sales]).

If I copy and paste the formula itself  (Sum(....)) into the expression editor it works as expected.

I would appreciate any guidance. 

[stevejoyce · Accepted solution]

the load script tries to evaluate the dollar sign expanded.  you can have placeholder characters and use replace, but it's cleaner and easier to manage use this nifty trick using mapping load + mapsubstring

mapping_specialchars:
mapping load * inline [
from, to
@, $
|, '
]
;

Let vMyformula=mapsubstring('mapping_specialchars', 'Sum({<Year={@(=MAX({$<[Sales] -= {|=(NULL())|} >}Year))}>}[Sales])');

[jelindbe]

That one did the trick! Thank you!