Wrong date in the report

[Anonymous]

Hi All,

I have a situdation where I have a colun "Confirmation Date" in my report.

In the desktop everything is fine but when I publish the data to service, all the places were confirmation date is blank auto fills a date i.e 12/30/1899 and I'm not sure why. Ideally I should be blank in the service also.

Anyone has idea about this?

Desktop View:

Service View:

[v-kelly-msft]

Hi  @Anonymous ,

It seems an isolated issue,when I try to reproduce your senario,I failed.Everything seems work fine.

In desktop:

And in Service:

It seems a date format issue from your DB,check below similar thread for help:

https://social.technet.microsoft.com/Forums/en-US/86feba4d-75ca-4a35-aa85-18d85bfacede/default-date-18991230-000000000?forum=sqlanalysisservices

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

In Power Query Editor I'm getting the column values like below:

This value 1/1/0001 is coming directly from DB and is showing blank at desktop when i'm formating the field to mm/dd/yyyy but not sure why on the service its showing like 12/30/1899.

[v-kelly-msft]

Hi  @Anonymous ,

Seems someone has posted the same issue before:

https://community.powerbi.com/t5/Desktop/Date-formatting-01-01-01-1899-12-31/td-p/1733174

Try to take below steps to solve it:

Go to power query from desktop and replace "1/1/0001" with null and try again.Finally publish to Service.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Should I change the data type of the column from Date to Text because I dont think so I can replace the values with NULL just like that or should I create a duplidate column and then perform the steps.

[v-kelly-msft]

Hi  @Anonymous ，

Yes,you could change the type to Text.Have a try and let me know the result.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Its showing up like below:

I'm not sure if its the right way to show to the client. I'd rather have blank in place of NULL but its ot allowing to show blank.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Do the transformations in desktop,once you have changed the date to text then replace the values as below :

And you will see:

Finally publish to Service and check again.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Yup it worked by creating a duplicated column, changing the data type to text and then replacing the values with blank but still not sure why this issue came in the first place as never got this before and have worked on ample to report with the dates.

[Greg_Deckler]

@Anonymous 12/30/1899 is the reference date, aka 0 in DAX. Seems like a bug in the service. Is this a calculated column or measure?

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[Anonymous]

It is neither a calculated column or a measure. Its a column directly coming from the DB view.