How to make condition for negative values?

[bourne2000]

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?

[amitchandak]

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

)

[bourne2000]

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

[amitchandak]

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

)

[bourne2000]

@amitchandak @Pragati11  Sorry it's not working. I don't get how the condition works for negative values. 

[Pragati11]

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

[bourne2000]

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

[Pragati11]

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]

@Pragati11  Thanks Please check the dax measures

No of days = DATEDIFF( 'Expiration Days'[Current Expiration Date],TODAY(), DAY)


Status = 
IF('Expiration Days'[No of days] > 0 && 'Expiration Days'[No of days] =60, "Expired within 60 days",
IF( 'Expiration Days'[No of days] <= 0 && 'Expiration Days'[No of days] <= -7, "Expire in 1 week", "No"))

​

Condition not working for negative values. I am getting will expire within one week for all the negative values

Please let me know

[Pragati11 · Accepted solution]

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