Data Format in Power BI

[JonathanJohns]

Good afternoon,

I've got a problem when I use my database in Power BI with the date.

The format date is like that in my database : 

"17/11/16" 

"17/11/21" 

So the format is : Year/Month/Day

But when I use this database in Power BI, the sotware read it like that : Day/Month/Year

So the date 17/11/21 which means the 21 november 2017 becomes the 17 november 2021.

I don't find the way to change that. I have tried to change the format of the date but the structure is always wrong.

Do you have any ideas to change it please ? 

Thanks for your help.

[Greg_Deckler]

Might be a regional settings issue.

You could always try to import it as text and then create a column using DATEVALUE that parses out the date parts, like:

MyDate = DATE(LEFT([column],2),MID([column],4,2),RIGHT([column],2))

[JonathanJohns]

Hi smoupre,

Thank you for your answer. I tried almost all the regional settings but I didn't find the solution. I tried your calculation and it works thank you. Just one thing, when I get the format with my date, I have something like that :

"21/10/17"

If I want to have all the year, the software doesn't write 21/10/2017 but 21/10/1917. Do you know a way to transform it please ? 

Thanks a lot for the calculation.

[Greg_Deckler]

Probably use something like a FORMAT or use the predefined format options under the modeling tab.

Column = FORMAT([Date],"mm/dd/yyyy")

[JonathanJohns]

If I change the format, it will change only the structure of the date but not the value of it. Moreover, using the function format gives me a date like a texte and not like a date. I am trying to change a little bit your calculation but I don't find how get the good structure now.

[v-ljerr-msft]

Hi @JonathanJohns,

How about:

MyDate = DATE(2000+VALUE(LEFT([column],2)),MID([column],4,2),RIGHT([column],2))

---

Have you tried the solution provided by @Greg_Deckler above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[JonathanJohns]

Hi,

Sorry I forgot to give back an answer.

The formule by @Greg_Deckler doesn't work.

This formula works : MyDate = DATE(VALUE(LEFT('25sqsyn'[DATE];2));MID('25sqsyn'[DATE];4;2);RIGHT('25sqsyn'[DATE];2))

But I still have the wrong year.

But when I add the number 2000 before the value, the software says : A  type of data from an argument with the formula DATE is wrong or the result is too big or too small.

Maybe it comes from the first argument because it is not a text format ? What do uou think about that please ?

Regards

[Greg_Deckler]

Strange, I created an Enter Data query with 17, 16, 15 and 14 in a column and created the following column:

Column = DATE(2000+VALUE([Year]),1,1) 

Didn't get that error, perhaps there is something in your data that is causing an issue?

[JonathanJohns]

I give you an example of my data. In fact, there is only two days :

- 16/11/17

- 21/11/17

DATE

17/11/2016

17/11/2016

17/11/2021

17/11/2021

17/11/2016

17/11/2016

17/11/2021

17/11/2021

17/11/2016

17/11/2016

17/11/2021

17/11/2021

17/11/2016

17/11/2016

17/11/2021

17/11/2021

[v-ljerr-msft]

Hi @JonathanJohns,

Could you try the formula below to see if it works? 

MyDate = 
DATE ( 2000+ VALUE ( RIGHT( '25sqsyn'[DATE], 2 ) ), MID ( '25sqsyn'[DATE], 4, 2 ), LEFT( '25sqsyn'[DATE], 2 ) )

Regards

[JonathanJohns]

I've got the same error. I show you how my data are. I don't see where the problem could come. This is a view from the editor.

[Greg_Deckler]

So, is that format, dd/mm/yyyy?

[Greg_Deckler]

How about:

MyDate = DATE(2000+VALUE(LEFT([column],2)),MID([column],4,2),RIGHT([column],2))