DateDiff With Prefilled NULL Value = 1/1/1900

[Anonymous]

I am struggling with modeling my data due to outliers. Think of this scenario, you check out a library book on a specific day (ActivationDateTime) and when you return the library book on a specific day (EndDateTime). The date diff between these 2 dates are easy. My problem is that my database will not have NULL/Blank dates for any EndDateTimes that are still outstanding (think library book that has NOT been returned). It will prefill with 1/1/1900 12:00:00 AM until the library book has been returned. 

ANTICIPATED SOLUTION: Is there a way for me to change a DATEDIFF function to output a NULL value if the prefilled date is 1/1/1900 12:00:00 AM? 

- I tried the replace function and that did not work.

Any support will be greatly appreciated!

ActivationDateTime	EndDateTime	ActiveEndDATEDIFF	ActiveEndTIMEDIFF
9/12/2017 10:00	9/12/2017 14:08	0	4
1/9/2018 17:07	1/10/2018 17:27	1	16
6/12/2019 20:40	1/1/1900 0:00	-43626	-1047044	<- CURRENT OUTPUT
6/12/2019 20:40	1/1/1900 0:00			<- ANTICIPATED OUTPUT

[Cmcmahan · Accepted solution]

As far as fixing in PowerQuery, if you open the Query Editor, right click the cell with the offending data, choose to Replace Values, and replace all 1/1/1900 values with nothing.

[Anonymous]

Probably easiest to fix this in Power Query, is that an option?

[Cmcmahan]

Power Query is the best option for modeling and cleaning up your data, but if for some reason you can't, it can be done via DAX this way:

ActiveEndDATEDIFF = IF(EndDateTime=1/1/1900, NULL, DATEDIFF(ActivationDateTime, EndDateTime, DAY))

[Anonymous]

@Anonymous  thanks for your response. I tried in Power Query but no success. If you have a solution...I would love to try it!

[Cmcmahan · Accepted solution]

As far as fixing in PowerQuery, if you open the Query Editor, right click the cell with the offending data, choose to Replace Values, and replace all 1/1/1900 values with nothing.

[Anonymous · Accepted solution]

see if the attached file helps. Not entirely sure how you got a couple of your outputs, but maybe this could help.  final table:

file:

https://1drv.ms/u/s!Amqd8ArUSwDS3BIJOUl7R63CsWP7?e=iLyE6E