Forum Discussion
DATEDIFF calculation error
- 9 years ago
OK, good news! DateDiff doesn't freak out over Nulls, it just returns another Null. (See screen shot 3). It also has no issues with days equal to each other... (Sorry for those wrong paths). (also screen shot 3).
This is my formula if you want NULLS to pass thru for no End Date. (Best practics)
DateDiff2 = IF(Table1[Start_Date]<=Table1[End_Date],DATEDIFF(Table1[Start_Date],Table1[End_Date],DAY),(DATEDIFF(Table1[End_Date],Table1[Start_Date],DAY)*-1))
This is another option if you want 0 instead of NULL for a null end date.
DateDiff2 = IF(ISBLANK(Table1[End_Date]),0,IF(Table1[Start_Date]<=Table1[End_Date],DATEDIFF(Table1[Start_Date],Table1[End_Date],DAY),(DATEDIFF(Table1[End_Date],Table1[Start_Date],DAY)*-1)))
See if this works and sort the result ascending... i'm curious what PowerBI is seeing that's still making it think the dates are reveresed... (Please let me know, I'm sucked into the mystery now...)
FOrrest
Can you screen shot me some of your data containing the null values? I want to duplciate on my side to troubelshoot.. FOrrest
Here you go. Thank you for helping me.