Forum Discussion
Calculate function with condition on numeric value (previously based on DateAdd)
I`m trying to adjust the following formula after the column type I am using changed from date to Numeric. Here is the previous calcualtion - which is working fine: -- Target = var T= CALCULAT...
Hi Anonymous
Please tryTarget = VAR CurrentNew = MAX ( 'metrics'[New] ) VAR T2 = CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew + 2 ) * 0.1 VAR T_2 = CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew - 2 ) * 0.1 RETURN COALESCE ( T2, T_2 )
Hi Anonymous
Please try
Target =
VAR CurrentNew =
MAX ( 'metrics'[New] )
VAR T2 =
CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew + 2 ) * 0.1
VAR T_2 =
CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew - 2 ) * 0.1
RETURN
COALESCE ( T2, T_2 )tamerj1
Thank you, it works like a charm.
An additional question I have, why doesn`t the measure work if I place it on another chart with a different x-axis?
I`m replacing the [New] column on this new chart with a text field [New FY] that has 1 to 1 values as such in the same table:
1 FY1
2 FY2
3 FY3Anonymous
Please try?Target = VAR CurrentNew = MAX ( 'metrics'[New] ) VAR T2 = CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew + 2, ALL ( 'metrics' ) ) * 0.1 VAR T_2 = CALCULATE ( SUM ( metrics[Sales] ), 'metrics'[New] = CurrentNew - 2, ALL ( 'metrics' ) ) * 0.1 RETURN COALESCE ( T2, T_2 )tamerj1
Works perfectly, I just changed ALL to ALLSELECTED cause I have some filters applied.
Many thanks