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.
nandic's avatar
nandic
Resident 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_
    Helper 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
      Resident 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.
      • donodackal's avatar
        donodackal
        Helper I
        4 years ago

        Hi Nandic

        I tried using your solution and Power Query is throwing me an error. I created a new custom column and entered this Fx

         

        On executing it, it throws an error "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."