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.
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 |
Solved! Go to 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.
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:
Probably easiest to fix this in Power Query, is that an option?
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 thanks for your response. I tried in Power Query but no success. If you have a solution...I would love to try it!
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.
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:
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |