Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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 )
Thanks for posting solution, it helped me solve my problem
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:
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)
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))?
@Cazzagg In that case you don't use DATEDIFF. Instead use below code.
= 1. * (YOURTABLE[StartDate]-YOURTABLE[EndDate])
@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?
@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
@Cazzagg No problems. Glad you got what you wanted with help of @Sean and @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.
@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"
@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 )
@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
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |