cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

DateDiff With Prefilled NULL Value = 1/1/1900

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!

 

ActivationDateTimeEndDateTimeActiveEndDATEDIFFActiveEndTIMEDIFF 
9/12/2017 10:009/12/2017 14:0804 
1/9/2018 17:071/10/2018 17:27116 
6/12/2019 20:401/1/1900 0:00-43626-1047044<- CURRENT OUTPUT
6/12/2019 20:401/1/1900 0:00  <- ANTICIPATED OUTPUT
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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

 

Final Table.png

 

file:

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

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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

Highlighted
Anonymous
Not applicable

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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

Highlighted
Super User III
Super User III

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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))
Highlighted
Super User III
Super User III

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: DateDiff With Prefilled NULL Value = 1/1/1900

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

 

Final Table.png

 

file:

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors