cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Date from month/day/year to day/month/year

How can i do that to a column that have data in that way?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Re: Date from month/day/year to day/month/year

Hi @Fabianserranopi,

 

I am guessing that your data must be in a CSV or TEXT file ?

Until the Power BI team provide functionaility to work with UK dates, I suggest one of the following methods:

 

1. Create a reference table (Excel is the easiest) that will allow you to convert your UK format dates to US dates within PowerBI Desktop. This is the easiest way to do it and what I would recommend. In fact, you can then add new references columns such as Financial Qtr, MMM-YY formats etc. I provide this reference table to all our employees that use Power BI. 

 

2. Use DAX queries. It gets a bit complicated. So rather than show you one large DAX query, I will split into multiple columns/queries. The image below shows you the UKDate in the 1st column, and MyDate which has finally been converted into a date format. To do this, I had to convert UKDate into a US date.... Here are the queries for each column:

 

Year = right([UKDate],4)

FindFirstSymbol = SEARCH("/",Test[UKDate],1)

Day = LEFT(Test[UKDate],Test[FindFirstSymbol])

Month = MID(Test[UKDate],Test[FindFirstSymbol]+1,(Test[Length]-4)-Test[FindFirstSymbol])

Length = LEN(Test[UKDate])

MyDate = datevalue(CONCATENATE((CONCATENATE(Test[Month],Test[Day])),Test[Year]))

 

Capture.PNG

 

Please give me a kudos... I spent an hour on this for you... LOL 😛

 

Good Luck,

Daniel

 

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Date from month/day/year to day/month/year

I do not believe that there is a default format like that for Date Time columns, but you can create a new column with the following formula:

 

FormattedDate = FORMAT([Date],"dd/MM/yy")

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
New Member

Re: Date from month/day/year to day/month/year

The problem is: when I selected the column "Date" and used the "Data Type" option to transform the data from Text to Date many of the cells shows "error" (because the data in the date column format is mm/dd/yyyy). I don't know how to fix that and i couldn't fixed with your formula :(.

 

 

Problem.jpg

 

Highlighted
Impactful Individual
Impactful Individual

Re: Date from month/day/year to day/month/year

Hi @Fabianserranopi,

 

I am guessing that your data must be in a CSV or TEXT file ?

Until the Power BI team provide functionaility to work with UK dates, I suggest one of the following methods:

 

1. Create a reference table (Excel is the easiest) that will allow you to convert your UK format dates to US dates within PowerBI Desktop. This is the easiest way to do it and what I would recommend. In fact, you can then add new references columns such as Financial Qtr, MMM-YY formats etc. I provide this reference table to all our employees that use Power BI. 

 

2. Use DAX queries. It gets a bit complicated. So rather than show you one large DAX query, I will split into multiple columns/queries. The image below shows you the UKDate in the 1st column, and MyDate which has finally been converted into a date format. To do this, I had to convert UKDate into a US date.... Here are the queries for each column:

 

Year = right([UKDate],4)

FindFirstSymbol = SEARCH("/",Test[UKDate],1)

Day = LEFT(Test[UKDate],Test[FindFirstSymbol])

Month = MID(Test[UKDate],Test[FindFirstSymbol]+1,(Test[Length]-4)-Test[FindFirstSymbol])

Length = LEN(Test[UKDate])

MyDate = datevalue(CONCATENATE((CONCATENATE(Test[Month],Test[Day])),Test[Year]))

 

Capture.PNG

 

Please give me a kudos... I spent an hour on this for you... LOL 😛

 

Good Luck,

Daniel

 

View solution in original post

Highlighted
New Member

Re: Date from month/day/year to day/month/year

Thank you very much, DANIEL @djnww!!!

I appreciate the time you took to explain this to me :D, i really do!

Highlighted
Advocate II
Advocate II

Re: Date from month/day/year to day/month/year

I've had similar date problem before. Solved it by "Split Column - By delimeter" and chose "/" to be the sign to split the Date Column.

 

You can find this in the "Edit query - Transform" and above "Text column"

 

Result being 3 extra columns with day of month - month number and year number

 

From there it's simply trivial to combine the 3 into one valid date column

Highlighted
Advocate I
Advocate I

Re: Date from month/day/year to day/month/year

Otherwise there is a solution that literally takes 3-clicks:

 

In Power Query Editor, right-click on your column header / Change Type / Using Locale...

Capture.PNG

source:

https://community.powerbi.com/t5/Desktop/date-format-is-changed-in-power-bi-desktop/m-p/45385#M17618

Highlighted
Power BI Team
Power BI Team

Re: Date from month/day/year to day/month/year

Just a side note for locals, if you take the MM/DD/YYYY format and change it to Canadian locals, it still remains MM/DD/YYYY and not: DD/MM/YYYY although the latter format is more acceptable in Canada. When trying to use locals peek a local that in the country the same date format is always used. Like UK or Russia.  

Highlighted
Regular Visitor

Re: Date from month/day/year to day/month/year

This solved it for me. Thanks for that.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors