cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

@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

Highlighted

@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

3 REPLIES 3
Highlighted
Solution Sage
Solution Sage

@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

Highlighted

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

Highlighted

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors