Forum Discussion

selected_'s avatar
selected_
Icon for Helper IV rankHelper 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 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?
 
 
 

 

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.

6 Replies

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident Rockstar
    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
    )


    • selected_'s avatar
      selected_
      Icon for Helper IV rankHelper IV
      5 years ago

      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?

      • nandic's avatar
        nandic
        Icon for Resident Rockstar rankResident Rockstar
        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. 

  • NipawanV's avatar
    NipawanV
    Icon for Helper I rankHelper 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.