Issue with Formula

[paul_hallwood]

I'm trying to sum all the activity for a particular chosen financial year, but for some reason I can't get it to work.  Here is the formula:

=Sum({$<[FiscalYear]={$(varFiscalYear)}>} [Activity])

varFiscalYear is a variable that evaluates to 2016_17.  The error message I get is "Error: Error in set modifier adhoc element list: ',' or ')' expected" but I'm not too sure what the issue is.  Any help with this will be much appreciated.

[sunny_talwar]

May be try this:

=Sum({$<[FiscalYear]={$(=varFiscalYear)}>} [Activity])

[paul_hallwood]

No, that hasn't worked unfortunately.  Thanks for the speedy response though!

[paul_hallwood]

...if I substitute the varFiscalYear for '2016_17' the formula seems to work.  Here is the working formula:

=Sum({$<[FiscalYear]={'2016_17'}>} [Activity])

Would declaring varFiscalYear as a variable and then using it in a formula be causing it any problems at all???

[sunny_talwar · Accepted solution]

Try one of these:

=Sum({$<[FiscalYear]={'$(=varFiscalYear)'}>} [Activity])

=Sum({$<[FiscalYear]={'$(=$(varFiscalYear))'}>} [Activity])

[paul_hallwood]

Yes, it seems as though your first formula has done what I have wanted!  Brilliant - thank you very much for your responses, they've been much appreciated.

[sunny_talwar]

Brilliant