Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Wrong date in the report

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:

 

Himanshu_13_0-1634209517228.png

Service View:

 

Himanshu_13_1-1634209556862.png

 

1 ACCEPTED SOLUTION

Hi  @Anonymous ,

 

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

vkellymsft_0-1634692467428.pngvkellymsft_1-1634692478298.png

And you will see:

vkellymsft_2-1634692493987.png

 

Finally publish to Service and check again.

 

Best Regards,
Kelly

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

View solution in original post

10 REPLIES 10
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

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

In desktop:

vkellymsft_0-1634545364810.png

And in Service:

vkellymsft_1-1634545378775.png

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
Not applicable

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

 

Himanshu_13_0-1634548537872.png

 

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.

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
Not applicable

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.

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
Not applicable

Its showing up like below:

 

Himanshu_13_0-1634638866575.png

 

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.

Hi  @Anonymous ,

 

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

vkellymsft_0-1634692467428.pngvkellymsft_1-1634692478298.png

And you will see:

vkellymsft_2-1634692493987.png

 

Finally publish to Service and check again.

 

Best Regards,
Kelly

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

Anonymous
Not applicable

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.

 

 

Himanshu_13_0-1634739319999.png

 

Greg_Deckler
Super User
Super User

@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".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.