Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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= 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's avatar
    tamerj1
    3 years ago

    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 )

6 Replies

  • ichavarria's avatar
    ichavarria
    Solution Specialist
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        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 )