cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

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.
1.PNG2.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

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

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.
1.PNG2.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors