IF statement for DATEDIFF to handle an error

[Anonymous]

I'm calculating the number of DaysToCLose for a sales process. I have a CreatedDate and a ClosedDate to work with. My DAX is this: DaysToClose = DATEDIFF(Opportunities[CreatedDate],Opportunities[ClosedDate],DAY)

Sometimes the CreatedDate is after the CloseDate because of that's how sales works sometimes. When this happens I get and error in my column calculation: "In DATEDIFF function, the start date cannot be greater than the end date"

I need to create a solution to handle this error. I thought an IF statement could do it, but I don't know how to detect for the greater start date. When this error occurs DaysToClose =0. 

Thanks,

Jeff

[Sean · Accepted solution]

@Anonymous the SWITCH statement is internally converted into nested IFs but its much easier to write and read

DaysToClose = 
SWITCH (
    TRUE (),
    'Opportunities'[ClosedDate] < 'Opportunities'[CreatedDate], -1 * DATEDIFF ( 'Opportunities'[ClosedDate], 'Opportunities'[CreatedDate], DAY ),
    'Opportunities'[ClosedDate] > 'Opportunities'[CreatedDate], DATEDIFF ( 'Opportunities'[CreatedDate], 'Opportunities'[ClosedDate], DAY ),
    0
)

This should do it!

EDIT: I guess I didn't see this in your original post => When this error occurs DaysToClose =0

If by this you mean when the error occurs value should be zero you can modify the above column like this...

DaysToClose 2 = 
SWITCH (
    TRUE (),
    ISBLANK('Opportunites'[CreatedDate]), 0,
    'Opportunites'[ClosedDate] > 'Opportunites'[CreatedDate], DATEDIFF ( 'Opportunites'[CreatedDate], 'Opportunites'[ClosedDate], DAY ),
    0
)

The first formula will give you the differnce between Created and Closed:

+ difference if Created is before Closed and

- difference if Closed is before Created

plus you'll get blanks if either date is blank

The second formula will give you ONLY the + difference if Created is before Closed all else will get 0.

[Anonymous]

@Sean Thanks. I'd never worked with the SWITCH function. An elegant solution.