cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JeffatHero Frequent Visitor
Frequent Visitor

IF statement for DATEDIFF to handle an error

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: IF statement for DATEDIFF to handle an error

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

 

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.

 

DATEDIFF - 2016-12-02.png

2 REPLIES 2
Super User
Super User

Re: IF statement for DATEDIFF to handle an error

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

 

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.

 

DATEDIFF - 2016-12-02.png

JeffatHero Frequent Visitor
Frequent Visitor

Re: IF statement for DATEDIFF to handle an error

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