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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
selected_
Helper IV
Helper IV

Measure expire date

I have table with expite date for each products.

I wanted to make a conditional column with icon that shows those product under 30 days that expire it gonna have yellow icon and the less than 0 gonna be red icon and those products that have over 30 days expieration gonna have green icon with this measure

 

Expiration Status Val =
IF([ExpiryDate] < TODAY(), -1
, IF([ExpiryDate] > TODAY(), 31
, 30
)
)
 
But it dosen't seems work for the yellow icon, yellow appear only if it will be expire today. How can I fix it?
 
 
 

pri.png

 

2 ACCEPTED SOLUTIONS
nandic
Memorable Member
Memorable Member

@selected_ ,

Conditional formatting is correct.
In my opinion, you need to create different logic for calculated field.

Try this:

Expiration Status Val final =
IF(DATEDIFF(TODAY(),'Table'[Date], DAY)<0,1,  -- if expiration date is in the past return 1
  IF(DATEDIFF(TODAY(),'Table'[Date], DAY)<30,30, -- if expiration date is in next 30 days return 30
   31) -- else return 31
)

20200920 cond formatting 1.PNG

View solution in original post

@selected_ ,

Here are some examples:

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

----

Expiration category =
IF(
AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>0,DATEDIFF(TODAY(),'Table'[Date], DAY)<=30),"30 days",
IF(AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>30,DATEDIFF(TODAY(),'Table'[Date], DAY)<=60),"60 days",
IF(AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>60,DATEDIFF(TODAY(),'Table'[Date], DAY)<=180),"180 days"
)
)
)
 
---

Add a measure which will count number of products:
Countrows = COUNTROWS('Table')

Use one of first 2 calculations as calculated columns and use last one as measure.

View solution in original post

6 REPLIES 6
NipawanV
Helper I
Helper I

@nandic This is great example for my case too.  However, I also have the blank value in date field.  How is the formula should I add to check blank value.  I added one line below (in red) but doesn't work.  

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

Many thanks in advance. 
Anonymous
Not applicable

i'm getting the same error. 

nandic
Memorable Member
Memorable Member

@selected_ ,

Conditional formatting is correct.
In my opinion, you need to create different logic for calculated field.

Try this:

Expiration Status Val final =
IF(DATEDIFF(TODAY(),'Table'[Date], DAY)<0,1,  -- if expiration date is in the past return 1
  IF(DATEDIFF(TODAY(),'Table'[Date], DAY)<30,30, -- if expiration date is in next 30 days return 30
   31) -- else return 31
)

20200920 cond formatting 1.PNG

@nandic 

 

Thanks it worked. Is that possible to make another measure on amount product will expire within 30 days and 60 days and 180 days?

@selected_ ,

Here are some examples:

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

----

Expiration category =
IF(
AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>0,DATEDIFF(TODAY(),'Table'[Date], DAY)<=30),"30 days",
IF(AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>30,DATEDIFF(TODAY(),'Table'[Date], DAY)<=60),"60 days",
IF(AND(DATEDIFF(TODAY(),'Table'[Date], DAY)>60,DATEDIFF(TODAY(),'Table'[Date], DAY)<=180),"180 days"
)
)
)
 
---

Add a measure which will count number of products:
Countrows = COUNTROWS('Table')

Use one of first 2 calculations as calculated columns and use last one as measure.

Hi Nandic

I tried using your solution and Power Query is throwing me an error. I created a new custom column and entered this Fx

donodackal_0-1644984808944.png

 

On executing it, it throws an error "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.