Forum Discussion

oliverome's avatar
oliverome
Regular Visitor
2 years ago
Solved

Using DAX for converting dates to status categories

Hi all, I have a question about using DAX to convert dates into a corresponding status. I have a column with calibration due dates for some equipment. There are a few possibilities: - The equipment...
  • oliverome's avatar
    oliverome
    2 years ago

    Thank you again for your help! The first if-statement needed a slight modification, as it interpreted a blank cell as 0, therefore giving all blank cells the output 'urgent action required'. I have added an 'is not blank' statement to that line and now it works perfectly.

     

    Thanks!

     

    IsCalibrationDue = 

IF (  [calibration_due]  <= TODAY() && NOT(ISBLANK([calibration_due])), "Urgent Action Required",
IF (  [calibration_due]  >= EDATE(TODAY(), 3), "OK",
IF (  [calibration_due] > TODAY() && [calibration_due] < EDATE(TODAY(), 3), "Set up new Calibration",
IF ( [calibration_due] = BLANK(), "Set Up Calibration Date"))))

     

     

Joe_Barry's avatar
Joe_Barry
Icon for Solution Sage rankSolution Sage
2 years ago

Hi oliverome 

Please try the following

 

 

 

Status =
IF (  [calibration_due]  <= TODAY(), "Urgent Action Required",
IF (  [calibration_due]  >= EDATE(TODAY(), 3), "OK",
IF (  [calibration_due] > TODAY() && [calibration_due] < EDATE(TODAY(), 3), "Set up new Calibration",
IF ( [calibration_due] = BLANK(), "Set Up Calibration Date")

 

 

 

Joe

  • oliverome's avatar
    oliverome
    Regular Visitor
    2 years ago

    Hi Joe,

    Thanks for your help. Your code gave me an error as I don't have an 'incidents' column. I replaced those parts, and ended up with this:

     

    IsCalibrationDue =
    IF (  [calibration_due]  <= TODAY(), "Urgent Action Required",
    IF (  [calibration_due]  >= EDATE([calibration_due].[Date], 4), "OK",
    IF (  [calibration_due] > TODAY() && [calibration_due] < EDATE([calibration_due].[Date], 4), "Set up new Calibration",
    IF ( [calibration_due] = BLANK(), "Set Up Calibration Date"))))
     
    This returns only 2 outputs: 
    - 'Set up new calibrations': this output appears for every cell that contains a date, regardless of when that date is.
    - 'Urgent action required': this output appears for all empty cells.
     
    Any ideas on how to proceed?
    • Joe_Barry's avatar
      Joe_Barry
      Icon for Solution Sage rankSolution Sage
      2 years ago

      Sorry, I edited afterwards as I seen an error, please give the below a shot

      Status =
IF (  [calibration_due]  <= TODAY(), "Urgent Action Required",
IF (  [calibration_due]  >= EDATE(TODAY(), 3), "OK",
IF (  [calibration_due] > TODAY() && [calibration_due] < EDATE(TODAY(), 3), "Set up new Calibration",
IF ( [calibration_due] = BLANK(), "Set Up Calibration Date")

       

      • oliverome's avatar
        oliverome
        Regular Visitor
        2 years ago

        Thank you again for your help! The first if-statement needed a slight modification, as it interpreted a blank cell as 0, therefore giving all blank cells the output 'urgent action required'. I have added an 'is not blank' statement to that line and now it works perfectly.

         

        Thanks!

         

        IsCalibrationDue = 

IF (  [calibration_due]  <= TODAY() && NOT(ISBLANK([calibration_due])), "Urgent Action Required",
IF (  [calibration_due]  >= EDATE(TODAY(), 3), "OK",
IF (  [calibration_due] > TODAY() && [calibration_due] < EDATE(TODAY(), 3), "Set up new Calibration",
IF ( [calibration_due] = BLANK(), "Set Up Calibration Date"))))