Calculate function with condition on numeric value (previously based on DateAdd)

[Anonymous]

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= CALCULATE(SUM(metrics[Sales]),DATEADD('metrics'[Date],+2,YEAR))*0.1

return

IF(T<>0,T,CALCULATE(SUM(metrics[Sales]),DATEADD('metrics'[Date],-2,YEAR))*0.1)
--

I no longer have that [Date] column, I Instead have a numeric field (which i need to keep numeric) and should replace it in the formula to become something like:
IF(T<>0,T,CALCULATE(SUM(metrics[Sales]),DATEADD('metrics'[New],-2,YEAR))*0.1)
Obviously it`s not working and I was wondering how can I make the formula function again. 

Thanks

[tamerj1 · Accepted solution]

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 )

[Anonymous]

@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       FY3

[tamerj1]

@Anonymous 
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 )

[Anonymous]

@tamerj1 
Works perfectly, I just changed ALL to ALLSELECTED cause I have some filters applied. 

Many thanks

[ichavarria]

Hi @Anonymous,

If you are using a numeric field instead of a date field in your formula, you cannot use the DATEADD function to add or subtract years from the value in the column. Instead, you need to use a mathematical operation to subtract the appropriate number of years from the value in the column.

Assuming your numeric field is called 'New', the adjusted formula should be:

Target =
VAR T = CALCULATE(SUM(metrics[Sales]), 'metrics'[New] + 2)*0.1
RETURN IF(T <> 0, T, CALCULATE(SUM(metrics[Sales]), 'metrics'[New] - 2)*0.1)

In this formula, we use the plus and minus operators to add or subtract the number of years from the value in the 'New' column. Note that we use single quotes around the column name, as we are referring to a column in the same table.

I hope this helps!

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Anonymous]

Thanks Isaac. 

The issue I`m facing with the formula is that it`s returning the value as if we are not subtracting or adding 2.
Sample data and results vs required results are:
New    Sales     Target               Desired Values of Target
1            20           2                           6
2            40           4                           8
3            60           6                          10
4            80           8                           4
5            100        10                          6