cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors