topic Re: Calculate function with condition on numeric value (previously based on DateAdd) in DAX Commands and Tips

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

Anonymous — Thu, 16 Mar 2023 16:51:17 GMT

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

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

ichavarria — Thu, 16 Mar 2023 16:59:01 GMT

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.

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

Anonymous — Thu, 16 Mar 2023 17:46:41 GMT

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

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

tamerj1 — Fri, 17 Mar 2023 10:01:44 GMT

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 )

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

Anonymous — Fri, 17 Mar 2023 10:40:13 GMT

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

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

tamerj1 — Fri, 17 Mar 2023 10:45:27 GMT

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 )

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

Anonymous — Fri, 17 Mar 2023 10:54:24 GMT

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

Many thanks