cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

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

1 ACCEPTED SOLUTION
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

9 REPLIES 9
Super User

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

)

Helper V

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

Super User

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

)

Helper V

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

Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Helper V

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

Super User

Hi @bourne2000 ,

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Helper V

@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

Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.