Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Dehlilah
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
Greg_Deckler
Super User
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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Dehlilah
Regular Visitor

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

Greg_Deckler
Super User
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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors