Whole Number to Year

[Anonymous]

Hi,

I have a year in a table (SQL Server defined as Int).  But when I imported into Power BI, it is taking as Whole Number as shown in the picture.

Solution 1:

So I changed DataTyep to 'Date' and as soon as I did it is converting Years as  below:

2017 --> 7/9/1905 

2018 -->  7/10/1905

2019 --> 7/11/1905

Solution 2:

  as mentioned in other posts, I tried to add a column from Example, but still same outcome.

I also verified Options/Regioanl Setting and it is set to United States.

Thank you,

Kumar

[v-shex-msft]

HI @Anonymous ,

I can't reproduce your issue, can you please share more details information to help us clarify and reproduce this?

For example: 

Database version, client version, what connector used to get data...
In addition, if you use t-sql query in advanced option to get table records, did these date field receive succeed? If you change column type to text, did it display full date values?

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the response,Xiaoxin. Please find attached link for data spreadsheet and pbix file.

https://1drv.ms/u/s!Ai0GzdHCLgzPkkCWfVNW4XlZ5Rt3?e=7QePj3

[v-shex-msft]

HI @Anonymous ,

I can't use your link, it only redirect and display a authentication website for signin account.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for looking into this ,Xiaoxin.

yes, I removed the link sharing as I found the solution.

[Anonymous · Accepted solution]

I added a new column and derived date using

DateKey = DATE(SalesFact[Snapshot_Year],SalesFact[Snapshot_Quarter]*3,1) to get Date from year and Quarter fields and joined with manual date/Calendar table as FK.

Thank you,

Kumar