cancel
Showing results for
Did you mean:
Highlighted 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? 2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted 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
) Highlighted Solution Sage

@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.
3 REPLIES 3
Highlighted 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
) Highlighted Helper II

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 Solution Sage

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