cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbolivar Frequent Visitor
Frequent Visitor

Parameter What If

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
Established Member

Re: Parameter What If

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.

2 REPLIES 2
AkhilAshok Established Member
Established Member

Re: Parameter What If

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.

Community Support Team
Community Support Team

Re: Parameter What If

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