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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Sab
Helper V
Helper V

UNION returns strange dates

Hello!

 

I am a table with around 400k rows (Sales), and another table with 15k (sales Current Month). 

 

As you can see from the screenshot, the dates are normal in the first two tables, and when I use the UNION function, it is expected to have the same dates (combined from those two tables). But as you can see something strange happens and I get dates dating from 1899!

 

Any idea why is this happening? There are NO empty values in either date column!


Thank you@

 

Screenshot 2024-11-13 003335.png

1 ACCEPTED SOLUTION
Sab
Helper V
Helper V

thank you for your replies, I found the 'issue'!

 

It was a column which for whatever reason was misplaced compared to the same column in the other table. 

 

In Power Query they were properly ordered, but after loading one of them went off one position! I tried to reorder that column in Power Query by sending it to the beginning (in both tables), didn't work! I had to get rid of it and after that, the dates look fine. 


Thanks again for your replies!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Sab ,

 

Did your problem has been solved? If so, please mark it as the correct solution, and point out if the problem persists.

 

Best regards,

Adamk Kong

Sab
Helper V
Helper V

thank you for your replies, I found the 'issue'!

 

It was a column which for whatever reason was misplaced compared to the same column in the other table. 

 

In Power Query they were properly ordered, but after loading one of them went off one position! I tried to reorder that column in Power Query by sending it to the beginning (in both tables), didn't work! I had to get rid of it and after that, the dates look fine. 


Thanks again for your replies!

Uzi2019
Super User
Super User

Hi @Sab 

 

Try to set both the date column to some different format. make sure it is date filed. and then craete new table and write your union dax fucntion. 

It will work.

 

I hope I answered your question!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Ritaf1983
Super User
Super User

Hi @Sab 

I tested a similar setup on my end, and the UNION function worked correctly without any date issues. If you're still experiencing strange dates, I recommend checking the data types of both columns in Power Query to ensure they’re explicitly set as Date/Time. Sometimes, mismatches in data types can cause unexpected results in DAX functions.

If everything seems correct with the data types and the issue persists, try refreshing the query or clearing any applied transformations that might impact the date format.

Ritaf1983_2-1731467690699.png

Ritaf1983_0-1731468265168.png

 

The pbix with the test is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors