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.
Anonymous's avatar
Anonymous
Not applicable
4 years ago

i'm getting the same error.