Parameter What If

[jbolivar]

Hello friends.

According to the function indicated below, a calculation is being made to a specific year (2019).

AcumulaPeriodos Unds = //
VAR
CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]);DATA[AÑO FISCAL]="2019")
VAR
DiferenciaMeses= 12 - CuentaMeses
RETURN
IF(DATA[AñoMax]="2019";CALCULATE([mTOTALVENTAS Und.];DATA[AÑO FISCAL]="2019");CALCULATE(SUM(DATA[Unidades]);PARALLELPERIOD(DATA[Fecha];-DiferenciaMeses;MONTH)))

I need to replace that year through a parameter, which I have already created. But when I replace it I get the following error

1.- When I place the parameter in the function Calculate:
A function 'CALCULATE' has been used in a True / False expression that is used as a table filter expression. This is not allowed.

2.- When I put it in the IF function:

DAX comparison operations do not support comparing values of the type TEXT with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the value.

I do not know what function to use to change the value from Numeric type to Text type.

Thank you

[v-juanli-msft]

Hi  @jbolivar 

I test on my side, create a what if parameter to add whole number from -10 to 5 to the specific year 2019.

From your post, it seems the columns DATA[AÑO FISCAL] and [AñoMax] are set to type Text. 

If you could accept to transform them to type Number, the function below works 

AcumulaPeriodos Unds = 
VAR     CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]),
          FILTER(DATA,DATA[AÑO FISCAL]=2019+[Parameter Value]))
VAR     DiferenciaMeses= 12-CuentaMeses
RETURN  IF(MAX([AñoMax])=2019+[Parameter Value],CALCULATE([mTOTALVENTAS Und.],
            FILTER(DATA,DATA[AÑO FISCAL]=2019+[Parameter Value])),
            CALCULATE(SUM(DATA[Unidades]),PARALLELPERIOD(DATA[Fecha],-DiferenciaMeses,MONTH)))

If you need keep the columns DATA[AÑO FISCAL] and [AñoMax] type Text, functions below works

Measure = FORMAT(2019+[Parameter Value],"General Number")

AcumulaPeriodos Unds = 
VAR     CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]),
          FILTER(DATA,DATA[AÑO FISCAL]=[Measure]))
VAR     DiferenciaMeses= 12-CuentaMeses
RETURN  IF(MAX([AñoMax])=[Measure],CALCULATE([mTOTALVENTAS Und.],
            FILTER(DATA,DATA[AÑO FISCAL]=[Measure])),
            CALCULATE(SUM(DATA[Unidades]),PARALLELPERIOD(DATA[Fecha],-DiferenciaMeses,MONTH)))

Best Regards

Maggie

[AkhilAshok · Accepted solution]

You can create a variable in the starting of the measure and use this variable everywhere:

VAR SelectedYear = FORMAT(SELECTEDVALUE(Paremeter[Paramter]), "####")

Format function will convert the Integer to a Text.