topic Re: Using DAX for converting dates to status categories in DAX Commands and Tips

Using DAX for converting dates to status categories

oliverome — Mon, 03 Jun 2024 12:34:12 GMT

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 has a date 4 or more months in the future. All is good.

- The equipment has a date less than 4 months in the future. I need to set up a new calibration.

- The equipment has a date in the past. This is not good, urgent action is needed.

- The cell is blank. There is no information available on the calibration due date. Also not good, but a different kind of action is needed.

How do I write DAX code to make this distinction?

I have tried this, for example:

IsCalibrationDue =

IF(

[calibration_due] <= TODAY() || [calibration_due] <= EOMONTH(TODAY(), 4),

TRUE,

FALSE

)

This returns True if a cell is blank, or the calibration date is in the past or within the next 4 months. False if the date is further in the future. But that's not all I need. I need more categories, but I can't figure it out.

Can you help me? I am new to Power BI and DAX, so I'd really appreciate it.

Re: Using DAX for converting dates to status categories

Joe_Barry — Mon, 03 Jun 2024 13:13:39 GMT

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

Re: Using DAX for converting dates to status categories

oliverome — Mon, 03 Jun 2024 13:16:52 GMT

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?

Re: Using DAX for converting dates to status categories

Joe_Barry — Mon, 03 Jun 2024 13:18:50 GMT

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

Re: Using DAX for converting dates to status categories

oliverome — Tue, 04 Jun 2024 06:39:51 GMT

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