Forum Discussion

LABrowne's avatar
LABrowne
Icon for Helper II rankHelper II
2 years ago
Solved

DAX: How use dates in an IF function

Hi there,

 

Am currently working on an if function and have completed most of it, please see below:

 

VAR = IF( [QualityCheck(%)] > 1 && Order[NumberProductsOrdered] >= 2, "Green", BLANK())
 
Orders have an expiry date a year after delivery. I need another condition within the logical test bit which is that DONT consider orders within 3 months of it's expiry date. Expiry date is it's own column.
 
Any help greatly appreciated.
 
Kind regards,
Luke
  • WinterMist's avatar
    WinterMist
    2 years ago

    LABrowne 

     

    Please try something like the following:

     

    VAR = 
    IF( 
    [QualityCheck(%)] > 1 
    && Order[NumberProductsOrdered] >= 2
    && TODAY() < 'Table'[Expiry Date] - 90, --TODAY IS BEFORE 90 DAYS LEADING UP TO THE EXPIRY DATE
    "Green", 
    BLANK()
    )
     
    NOTE: In this example, I used TODAY().  However, if you need to compare it to a different date (i.e. Order[Order Date]), then simply switch to the desired date.
     
    Regards,
    Nathan

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi LABrowne ,

     

    What do you mean by "DONT consider orders within 3 months of it's expiry date", or what should be return in this case?

  • WinterMist's avatar
    WinterMist
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    LABrowne 

     

    Please try something like the following:

     

    VAR = 
    IF( 
    [QualityCheck(%)] > 1 
    && Order[NumberProductsOrdered] >= 2
    && TODAY() < 'Table'[Expiry Date] - 90, --TODAY IS BEFORE 90 DAYS LEADING UP TO THE EXPIRY DATE
    "Green", 
    BLANK()
    )
     
    NOTE: In this example, I used TODAY().  However, if you need to compare it to a different date (i.e. Order[Order Date]), then simply switch to the desired date.
     
    Regards,
    Nathan