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

Regular Visitor

## IF(DATEDIFF( ....

I am really close to my desired outcome, but need to figure out a small change. I have a product that expires after two years. I have data on the order date, so I made a column called "Expiration Date" which is simply two years after the order date.

I created this coding:

Expiration Category v2 =
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<0,"Expired",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=30,"Expires in 30 days",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=60,"Expires in 60 days",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=180,"Expires in 180+ days", "Expires in 180+ days"
))))

I want to know the amount of quantity that has expired, will expire soon (30 days or in 60 days) and 180+, but really I could change the last outcome 180+ to anything else or >61 days from expiration. Can someone help with this change? As I have it now the code doesn't capture total shippments, so something is off.

Thank you!
1 ACCEPTED SOLUTION
Super User

@Dehlilah So, a few possible improvements:

``````Expiration Category v2 =
SWITCH(TRUE(),
( TODAY() - [Expiration Date] ) * 1. < 0, "Expired",
( TODAY() - [Expiration Date] ) * 1. <= 30, "Expires in 30 days",
( TODAY() - [Expiration Date] ) * 1. <= 60, "Expires in 60 days",
"Expires in 180+ days"
)``````

So, SWITCH(TRUE(), ...) is much cleaner than nested IF statements. Two, using simple math keeps the computation out of the formula engine which is faster. Dates are the number of days since December 30th, 1899 so you can just use subtraction to get the number of days.

Depending on your data, you may want to reverse your logic. If the majority of the items are 180+ from expiring then you will want that condition first in your SWITCH statement for example. That way the majority of rows do not have to fail a bunch of other logical conditions before getting to the right one.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Regular Visitor

@Greg_Deckler , your solution works! Thank you for the code and the help!

Super User

@Dehlilah So, a few possible improvements:

``````Expiration Category v2 =
SWITCH(TRUE(),
( TODAY() - [Expiration Date] ) * 1. < 0, "Expired",
( TODAY() - [Expiration Date] ) * 1. <= 30, "Expires in 30 days",
( TODAY() - [Expiration Date] ) * 1. <= 60, "Expires in 60 days",
"Expires in 180+ days"
)``````

So, SWITCH(TRUE(), ...) is much cleaner than nested IF statements. Two, using simple math keeps the computation out of the formula engine which is faster. Dates are the number of days since December 30th, 1899 so you can just use subtraction to get the number of days.

Depending on your data, you may want to reverse your logic. If the majority of the items are 180+ from expiring then you will want that condition first in your SWITCH statement for example. That way the majority of rows do not have to fail a bunch of other logical conditions before getting to the right one.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...