SWITCH assuming a blank numeric column is zero - need help with syntax

[RichardJ]

Apologies for the rookie question but I cant figure out the correct syntax to generate values in a new column.

I think the issue I am having is due to the switch function identifying a Blank numeric column as being a zero value rather than being empty.

I have a numeric column which i'd like to apply criteria to as follows:

If the DrawingSet[Duration between Forecast and Approval] value is greater than zero then apply the "Delivered Early" value to the column.

If the DrawingSet[Duration between Forecast and Approval] value is equal to zero then apply the 'Delivered On Time" value to the column.

If the DrawingSet[Duration between Forecast and Approval] value is less than zero then apply the "Delivered Late" value to the column.

If the DrawingSet[Duration between Forecast and Approval] column is empty then apply the "No Data" value to the column

The column value is

Forecast Accuracy =

IF(DrawingSet[Duration between Forecast and Approval] <> BLANK(),

SWITCH(

TRUE(),

DrawingSet[Duration between Forecast and Approval] >0, "Delivered Early",

DrawingSet[Duration between Forecast and Approval] =0, "Delivered On Time",

DrawingSet[Duration between Forecast and Approval] <0, "Delivered Late"

),

"No Data")

The problem with the statement above is that it is applying "No Data" to the entries which should be "Delivered On Time"

I've tried this column value also:

Forecast Accuracy =

SWITCH(

TRUE(),

DrawingSet[Duration between Forecast and Approval] >0, "Delivered Early",

DrawingSet[Duration between Forecast and Approval] =0, "Delivered On Time",

DrawingSet[Duration between Forecast and Approval] <0, "Delivered Late"

)

The problem with this statement is that 'Delivered On Time' is applied to 'Blank' columns which is incorrect.

If possible i'd like to keep the column as numeric as it's used on lots of charts.

Any help would be appreciated!

Thanks,

Richard

[az38 · Accepted solution]

@RichardJ 

maybe this

Forecast Accuracy =
SWITCH(
TRUE(),
ISBLANK(DrawingSet[Duration between Forecast and Approval]), "No Data",
DrawingSet[Duration between Forecast and Approval] >0, "Delivered Early",
DrawingSet[Duration between Forecast and Approval] =0, "Delivered On Time",
DrawingSet[Duration between Forecast and Approval] <0, "Delivered Late",
"Undefined"
)

[RichardJ]

Thanks for making my day @az38. That worked perfectly - I wouldn't have worked out that approach.