Forum Discussion
Calculated Date Column Showing Underlying Date
This is a strange issue.
In a Table, I have a Last Received date column. Due to a quirk in the database, this column may contain invalid dates - 0001-01-01 or 9999-12-31. These dates, of course, cause problems with visuals and date calculations. So I created a new column using this code:
If I try and create a visual in the report using the Received to Stock column, I have three problems.
Problem 1 - by default, it displays as Date Hierarchy. This will show a date of December 30, 1899. Changing the display to just be Recieved to Stock changes the output to a Blank. I do not understand why this column still sees the underlying invalid date information if I have set it specifically to be blank.
Problem 2 - the Blank output only displays if the PO Number column is included in the visual, and only the PO Number column. If I add in any other columns, it reverts to the December 30, 1899 display.
Problem 3 - the Later or Early and the Days Early or Late columns both change their values based on in a visual to dispaly the information as calculated based on the Dec. 30, 1899 entry.
Anonymous,
It's best to do transformations in SQL or Power Query. Do you have the ability to write custom SQL? If not, you could create a custom column in Power Query:
if List.Contains({#date(1,1,1), #date(9999,12,31)}, [Last Received to Stock]) then null else [Last Received to Stock]
2 Replies
Anonymous,
It's best to do transformations in SQL or Power Query. Do you have the ability to write custom SQL? If not, you could create a custom column in Power Query:
if List.Contains({#date(1,1,1), #date(9999,12,31)}, [Last Received to Stock]) then null else [Last Received to Stock]