Forum Discussion

selected_'s avatar
selected_
Helper IV
5 years ago
Solved

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 gonn...
Need Help
  • nandic's avatar
    nandic
    5 years ago

    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
    )


  • nandic's avatar
    nandic
    5 years ago

    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.
NipawanV's avatar
NipawanV
Helper I
3 years ago

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.