Multiple Date Formats being displayed (BI mixing day and month)

[HanLee]

Hi guys, I've got some week data here that's not being fomatted correctly. 

The final 2 dates are being displayed as 2/05/2017 and 2/12/2017 then they should be 5/2/2017 and 12/2/2017 respectively while the other 3 dates are being shown correctly.

This is the data type and format being used for the data. The data comes from an excel table which also uses the date format for the cells if that makes a difference. 

Any help would be appreciated. 

Thanks,

Han

[v-yuezhe-msft · Accepted solution]

Hi @HanLee,

The issue is caused by that you enter wrong format of 02/05/17 and 02/12/17 in Excel table.  Power BI Desktop uses same rule to handle data of a column, since Power BI Desktop recognizes 15/01/17 as 15 Jan,2017,  it is an expected behavior that Power BI Desktop recognizes 02/12/17 as 12 Dec,2017  and recognizes 02/05/17 as 02 May,2017.

When you enter the following data of Excel to Power BI Desktop, you can get expected result.

Thanks,
Lydia Zhang