Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Date format

Hello everybody,

 

I have a question about my data. In my data a have a column called Birth date where all have this format : 

"Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)"

.....

"Sun Aug 19 1979 15:00:00 GMT-0700 (Pacific Standard Time)"

What I want is to creat a new column of birth date where be in this format "31/12/1964" and 19/08/1979

and then to calculate the age.

How can I do this ?

Thank you.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi can you try these steps? It does seem to work for me:

1) in Power BI click on Edit Queries in the Home Tab

2) Look for the birthdate column, click on it

3) Go to the Transform tab and click on Split Column

4) Choose "By delimiter" and select "Custom" in the dropdown menu that pops up

5) In the textfield below the dropdown, type in "GMT"

6) Pick "left-most delimiter"

7) Press OK

8.) Power BI will split your data in two different columns named Birthdate.1 and Birthdate.2

Birthdate.1 will be transform to a Date/Time type.

9) Don't forget to go to the home tab and press Close&Apply

 

These steps will result in the following DateTime column

 

Let me know if this works for you.

 

 

Birthdate.1

31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1949 15:00:00
31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1969 15:00:00
31-12-1959 15:00:00
31-12-1969 15:00:00
31-12-1989 15:00:00
31-12-1974 15:00:00
31-12-1988 15:00:00
31-12-1969 15:00:00
31-12-1993 15:00:00
31-12-1959 15:00:00
31-12-1979 15:00:00
31-12-1988 15:00:00
31-12-1998 15:00:00
31-12-1992 15:00:00
31-12-1949 15:00:00
31-12-1969 15:00:00
31-12-1959 15:00:00
31-12-1989 15:00:00
31-12-1987 15:00:00
31-12-1969 15:00:00
31-12-1964 15:00:00
31-12-1969 16:00:00
22-5-2001 17:00:00
31-12-1979 16:00:00
31-12-1994 16:00:00
31-12-2018 16:00:00
31-12-1989 16:00:00
10-7-1979 17:00:00
17-8-1971 17:00:00
31-12-2018 16:00:00
19-8-1979 17:00:00
31-12-1989 16:00:00
31-12-1938 16:00:00
31-12-2018 16:00:00
16-12-2019 16:00:00
16-12-2019 16:00:00
16-12-2019 16:00:00
19-12-2019 16:00:00
31-12-2018 16:00:00
25-12-2019 16:00:00

View solution in original post

v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try the following DAX, I think region is the problem.

Column = 
var a =SEARCH(" ",'Table'[birthDate],1)+1
var b = SEARCH(" ",'Table'[birthDate],5)+1
var c = SEARCH(" ",'Table'[birthDate],9)+1
var d = MID('Table'[birthDate],a,3)
var e = SWITCH(
    TRUE(),
    d="Dec",12,
    d="Nov",11,
    d="Oct",10,
    d="Sep",9,
    d="Aug",8,
    d="Jul",7,
    d="Jun",6,
    d="May",5,
    d="Apr",4,
    d="Mar",3,
    d="Feb",2,
    d="Jan",1
)
return
IF(c=12,
MID('Table'[birthDate],b,2),
0&MID('Table'[birthDate],b,1)
)
&"-"&e&"-"&MID('Table'[birthDate],c,4)

Here is the test file for your reference.

 

 

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

View solution in original post

23 REPLIES 23
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try the following DAX, I think region is the problem.

Column = 
var a =SEARCH(" ",'Table'[birthDate],1)+1
var b = SEARCH(" ",'Table'[birthDate],5)+1
var c = SEARCH(" ",'Table'[birthDate],9)+1
var d = MID('Table'[birthDate],a,3)
var e = SWITCH(
    TRUE(),
    d="Dec",12,
    d="Nov",11,
    d="Oct",10,
    d="Sep",9,
    d="Aug",8,
    d="Jul",7,
    d="Jun",6,
    d="May",5,
    d="Apr",4,
    d="Mar",3,
    d="Feb",2,
    d="Jan",1
)
return
IF(c=12,
MID('Table'[birthDate],b,2),
0&MID('Table'[birthDate],b,1)
)
&"-"&e&"-"&MID('Table'[birthDate],c,4)

Here is the test file for your reference.

 

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Hi @Anonymous 

I think all above solutinons are correct.

 

Just remove that GMT part from EDIT query-> modelling tab.

 

and then calculate AGE differance by using Yearfrac.

Don't use datediff as it doesn't give you correct DOB.

 

Thanks & regards,

Pravin Wattamwar.

https://www.linkedin.com/in/pravin-p-wattamwar/

 

If I resolve your problem mark it as solution and give kudos.

v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try this column:

Column =
VAR a =
    SEARCH ( " ", 'Table'[birthDate], 1 ) + 1
VAR b =
    SEARCH ( " ", 'Table'[birthDate], 5 ) + 1
VAR c =
    SEARCH ( " ", 'Table'[birthDate], 9 ) + 1
RETURN
    IF (
        c = 12,
        MID ( 'Table'[birthDate], b, 2 ),
        0 & MID ( 'Table'[birthDate], b, 1 )
    ) & "-"
        & MID ( 'Table'[birthDate], a, 3 ) & "-"
        & MID ( 'Table'[birthDate], c, 4 )

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Hi @v-eachen-msft 

 

thank you for your answer I have a small problem, how can I share my sample pbix with this Forum I didn't find it ?

 

Thank you

Hi @Anonymous ,

 

You could upload your files to OneDrive and share the link here.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Thank you.

You find attached my sample.

https://zhortech-my.sharepoint.com/:u:/p/m_fatnassi/ER2nrbw2_eJAtLNCoCkCDtoB_JEbrTYoQkCpT_5P3LnLEA?e... 

I think the problem that is two defferents message : 

"Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)"

"Tue Aug 17 1971 17:00:00 GMT-0700 (Pacific Daylight Time)"

 

Best regards,

Hi @Anonymous ,

 

Your file has limitation so I cannot download it.

You need to set it like the following image:

4-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Try creating a custom column with the power query editor instead and see if you get the same error

The code would be something like

Text.BetweenDelimiters([BirthDate]," ", " ",1,0) & "/" &
Text.BetweenDelimiters([BirthDate]," ", " ",0,0) & "/" &
Text.BetweenDelimiters([BirthDate]," ", " ",2,0)

 then change the type

 

Capture.PNG

 

Capture.PNG

Anonymous
Not applicable

I don't know why but isn't work.
BirthDate
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1949 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Tue Dec 31 1974 15:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1988 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time)
Fri Dec 31 1993 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 31 1979 15:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1988 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1998 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1992 15:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1949 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1987 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 31 1969 16:00:00 GMT-0800 (Pacific Standard Time)
Tue May 22 2001 17:00:00 GMT-0700 (Pacific Daylight Time)
Mon Dec 31 1979 16:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1994 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time)
Sun Dec 31 1989 16:00:00 GMT-0800 (Pacific Standard Time)
Tue Jul 10 1979 17:00:00 GMT-0700 (Pacific Daylight Time)
Tue Aug 17 1971 17:00:00 GMT-0700 (Pacific Daylight Time)
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time)
Sun Aug 19 1979 17:00:00 GMT-0700 (Pacific Daylight Time)
Sun Dec 31 1989 16:00:00 GMT-0800 (Pacific Standard Time)
Sat Dec 31 1938 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time)
Thu Dec 19 2019 16:00:00 GMT-0800 (Pacific Standard Time)
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time)
Wed Dec 25 2019 16:00:00 GMT-0800 (Pacific Standard Time)

Make sure the column is text

New date = mid(dateBirth[BirthDate],9,2) & "-" & mid(dateBirth[BirthDate],5,3) & "-" & mid(dateBirth[BirthDate],12,4)

 

check pbix https://www.dropbox.com/s/nwjzy01u4czu9uw/Dateformatissue.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

Anonymous
Not applicable

Hi can you try these steps? It does seem to work for me:

1) in Power BI click on Edit Queries in the Home Tab

2) Look for the birthdate column, click on it

3) Go to the Transform tab and click on Split Column

4) Choose "By delimiter" and select "Custom" in the dropdown menu that pops up

5) In the textfield below the dropdown, type in "GMT"

6) Pick "left-most delimiter"

7) Press OK

8.) Power BI will split your data in two different columns named Birthdate.1 and Birthdate.2

Birthdate.1 will be transform to a Date/Time type.

9) Don't forget to go to the home tab and press Close&Apply

 

These steps will result in the following DateTime column

 

Let me know if this works for you.

 

 

Birthdate.1

31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1949 15:00:00
31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1989 15:00:00
31-12-1969 15:00:00
31-12-1959 15:00:00
31-12-1969 15:00:00
31-12-1989 15:00:00
31-12-1974 15:00:00
31-12-1988 15:00:00
31-12-1969 15:00:00
31-12-1993 15:00:00
31-12-1959 15:00:00
31-12-1979 15:00:00
31-12-1988 15:00:00
31-12-1998 15:00:00
31-12-1992 15:00:00
31-12-1949 15:00:00
31-12-1969 15:00:00
31-12-1959 15:00:00
31-12-1989 15:00:00
31-12-1987 15:00:00
31-12-1969 15:00:00
31-12-1964 15:00:00
31-12-1969 16:00:00
22-5-2001 17:00:00
31-12-1979 16:00:00
31-12-1994 16:00:00
31-12-2018 16:00:00
31-12-1989 16:00:00
10-7-1979 17:00:00
17-8-1971 17:00:00
31-12-2018 16:00:00
19-8-1979 17:00:00
31-12-1989 16:00:00
31-12-1938 16:00:00
31-12-2018 16:00:00
16-12-2019 16:00:00
16-12-2019 16:00:00
16-12-2019 16:00:00
19-12-2019 16:00:00
31-12-2018 16:00:00
25-12-2019 16:00:00
Anonymous
Not applicable

Thank you very much @Anonymous @v-eachen-msft and all for your help.

 

Anonymous
Not applicable

Hi,

 

I can't access the link, but you can try to split the column with the date format.

If you split it at the "(" Power BI might be able to understand that it's a date.

You can do this in Power BI Desktop => Edit Queries => Transform tab => Split column => By delimiter.

 

Kind regards.

 

 

Anonymous
Not applicable

Sorry didn't work !

Anonymous
Not applicable

I tried it myself with the two dates you entered before.

If you also remove the GMT part, Power BI will automatically set it as a Date.

 

Kind Regards

amitchandak
Super User
Super User

New format =  format(date,"DD/MM/YYYY")

 

For age take date diff. Prefer orginal column in date format

Age = datediff(date,today(),YEAR)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous
Not applicable

HI amitchandak,

 

Before to calculate the age how can I transfer this type :

"Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)" to 31/12/1964 ?

 

Thank you.

Anonymous
Not applicable

Hi,

 

Can you try going to the column where you store the birtdate, then select it.

Go to the modeling tab and select Format in the Formatting Area.

There you should be able to pick the formatting you want.

 

 

 

Anonymous
Not applicable

Yes, I did this, but it not work.

The software can not read this type of format "Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)", and even if I choose format date in the modeling, i have a error.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.