How to use DATEDIFF when End Date is less than Start Date

[Cazzagg]

Hello

I have been using DAX DATEDIFF statement  to calculate No Days between two dates which are

Target Completion Date and Actual Completion Date

Which works all very well except now I have sometimes the Actual Completion Date is before the Target Completion Date (ie where an Action Officer performed better and completed the task before the "target" date).

However, of course DATEDIFF doesn't like this and gives error "In DATEDIFF function, the start date cannot be greater than the end date"

How can I get around this because in my instance this kind of situation will occur? I have searched and searched and searched the Internet because I didnt want to bother you guys but I just cannot find a workaround.

Any help greatly appreciated

FYI - My DAX formula is as per below (New Column) and I am connecting to a SQL Database:

No Days = DATEDIFF('Request Header'[Actual Completion Date],'Request Header'[Target Completion Date],DAY)

[anands06]

Thanks for posting solution, it helped me solve my problem

[Anonymous]

Dear All,

Could you help me? I tried to come up with the solution on my own, but I keep getting various errors.

I need to calculate the number of days we need to convert each lead from our list into a qualified lead.

I have two columns:

• Lead conversion date (always contains date and time)
• Qualified lead conversion date (contains date and time only if the lead was converted into qualified lead. It is empty otherwise).

DateDiff doesn't work for me because the Qualified lead conversion date column contains empty spaces and I get the error:

In DATEDIFF function, the start date cannot be greater than the end date

Here is my formula:

Time to convert into QL = DATEDIFF('3Leads'[Lead Conversion Date],'3Leads'[Qualified Lead Conversion Date],DAY)

[Silver_Rain]

This might be a silly way to do it, but could you do something like...

=iferror(datediff(column A),(column B),interval),-1*(datediff(column B),(Column A),interval))?

[ankitpatira]

@Cazzagg In that case you don't use DATEDIFF. Instead use below code.

= 1. * (YOURTABLE[StartDate]-YOURTABLE[EndDate])

[Cazzagg]

@ankitpatiraThank you for your response

That does indeed "kind" of work except I really would like to see as negative the value that is returned when the Action Officer completes the task before the target date

Is there any way I can get the statement to return values with negative in such cases?

[Cazzagg]

@ankitpatira -I think I owe you an apology too! I just exported my data out to Excel to see what I was getting with my calculated columns and it appears I was indeed getting -ve against the tasks that had been completed before target. I just couldnt see the -ve sign in PowerBI data modeller!

So either solution would have worked

[ankitpatira]

@Cazzagg No problems. Glad you got what you wanted with help of @Sean and @Greg_Deckler.

[Greg_Deckler]

No Days = IF('Request Header'[Actual Completion Date]<'Request Header'[Target Completion Date],-1*DATEDIFF('Request Header'[Target Completion Date],'Request Header'[Actual Completion Date],DAY),DATEDIFF('Request Header'[Actual Completion Date],'Request Header'[Target Completion Date],DAY))

Maybe something like that.

[Cazzagg]

@Greg_Deckler - I really really appreciate you taking the time to repsond.

Unfortundately I did try exactly as you suggested but still getting the DATEDIFF error "In DATEDIFF function, the start date cannot be greater than the end date"

[Sean · Accepted solution]

@Cazzagg This should work let me know...

Days =
SWITCH (
    TRUE (),
    'Table'[Actual] < 'Table'[Target], -1 * DATEDIFF ( 'Table'[Actual], 'Table'[Target], DAY ),
    'Table'[Actual] > 'Table'[Target], DATEDIFF ( 'Table'[Target], 'Table'[Actual], DAY ),
    0
)

[Cazzagg]

@Sean - thank you so much. That works a treat

Many thanks to everyone that contributed and helped so quickly. I really do appreciate all your assistance and responses

[Cazzagg]

I canned the idea of using DATEDIFF and am now using the formula below ...... which "seems" to work ok except I would like it to show as a negative where the officer has completed before the target date:

No Days = 1.0*('Request Header'[Actual Completion Date])-('Request Header'[Target Completion Date])