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