Forum Discussion
How to make condition for negative values?
Hi
I am having the below column
I created no of days column by taking the difference from today and above date.
No of days column
I want to calculate a new column called status with the following condition,
If no of days is within 60 days then I need to create "Expired within 60 days"
If no of days Will Expire < 2 Weeks from Today's date then I need to create “In 2 Weeks”
If no of days Will Expire < = 30 days from Today's date then I need to create “In 30 days”
If no of days Will Expire < = 60 days from Today's date then I need to create “In 60 days”
If no of days Will Expire < = 90 days from Today's date then I need to create “In 90 days”
I am able to create expired within 60 days using the switch function by creating logic greater than zero and less than or equal to 60. However, I am not sure how to create a logic for negative values which is not expired yet. Can anyone advise?
HI bourne2000 ,
Try something like below based on what I said earlier that negative conditions should be in your DAX in the starting:
Status = IF ( 'Expiration Days'[No of days] >= -7 && 'Expiration Days'[No of days] <= 0, "Expire in 1 week", IF ( 'Expiration Days'[No of days] > 0 && 'Expiration Days'[No of days] <= 60, "Expired within 60 days", "No" ) )Thanks,
Pragati
9 Replies
Hi bourne2000 ,
I would suggest putting the negative value condition as the first condition in your DAX formula and then add the other conditions for value greater them zero. Something like below:
testCol = IF(yourval < 0, "Not expired", IF(yourval > 0 && yourval <= 60, "Expired within 60 days", YOUR OTHER CONDITIONS) )Share your DAX formula if possible.
Thanks,
Pragati
Pragati11 Thanks for your reply. I need to make conditions for the negative value. Example, if my no of days is -1 to -14 that means in less than 2 weeks it will get expire. Not sure how to make this logic, if anything less than 0, it will consider all negative value.
Hi bourne2000 ,
I asked you to share your DAX. Can you please share that?
Something like below:
testCol = val minCol = MIN(No of days) RETURN IF(youtable[No of days] >= minCol && youtable[No of days] <= -14, "Not expired", IF(youtable[No of days] > 0 && youtable[No of days] <= 60, "Expired within 60 days", YOUR OTHER CONDITIONS) )Thanks,
Pragati
bourne2000 , Create a new column like
New column =
var _days = datediff(today(), Table[Date], day)
return
Switch(True(),
_day < 14, "In 2 Weeks” ,
_day < 30 , "In 30 days” ,
_day < 60, "In 60 days” ,
'_day < 90, "In 90 days” ,
"More than 90 days"
)
amitchandak Thanks for your reply. I need to make conditions for the negative value. For example, if my no of days is -1 to -14 that means in less than 2 weeks it will get expire. Not sure how to make this logic, if anything is less than 0, it will consider all negative values. The above switch function doesn't consider the logic for native values, right?
bourne2000 , Check what Pragati11 , suggested.
You can add negative conditions too
example
New column =
var _days = datediff(today(), Table[Date], day)
return
Switch(True(),
_day <30, "before 4 Weeks” ,
_day < 14, "before 2 Weeks” ,
_day < 0, "before today ” ,
_day < 14, "In 2 Weeks” ,
_day < 30 , "In 30 days” ,
_day < 60, "In 60 days” ,
'_day < 90, "In 90 days” ,
"More than 90 days"
)
