Variable String Month Name to Number and back again

[#1]

Hi,

I'm having a bit of trouble trying to use a user selection variable that is a string. I want to convert that string to a number, use the number to add 1 to it then turn it back into a string to query with. Basically we want to "forcast" the next months goals by looking at what the user selected.

Here is what I have so far it seems like it should work but I'm just getting "Zero" back.

vMonthSelection  is defined as:

=GetFieldSelections([Month])

(MAX({<[Monthly Goals.mon] = {(=vMonthSelection)}>}[Monthly Goals.nmbr mon]) + 1)

Returns a 4. when the user selects Jan, Feb, Mar.

SUM({< [Monthly Goals.nmbr mon] = {4} >}[Monthly Goals.dollar goal])

Returns the goal for the 4th month.

However

SUM({< [Monthly Goals.nmbr mon] = {MAX({<[Monthly Goals.mon] = {(=vMonthSelection)}>}[Monthly Goals.nmbr mon]) + 1)} >}[Monthly Goals.dollar goal])

Returns ZERO.

So I'm not sure what I'm doing wrong, I don't get any syntax errors either.

Any help would be wonderful.

Thanks

[Gysbert_Wassenaar · Accepted solution]

You need to use dollar expansion to evaluate the max expression. And you can simply use MAX(Month)

SUM({< [Monthly Goals.nmbr mon] = {$(=MAX(Month) + 1)} >}[Monthly Goals.dollar goal])

[Kushal_Chawda]

SUM({< [Monthly Goals.nmbr mon] = {"$(=vMonthSelection) + 1"} >}[Monthly Goals.dollar goal])

[jagan]

HI,

Try like this

SUM({< [Monthly Goals.nmbr mon] = {$(=MAX(Month) + 1)} >}[Monthly Goals.dollar goal])

Regards,

Jagan.