cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

How to convert date to year ?

Hi All

 

I have Raw data below :-

 

Date

12/2/2020

20/12/2019

 

My excepted result :-

Year,motn,Date

2020,2,12/2/2020

2019.12.2019

 

Hoep some one can advise me.

 

Paul Yeo

9 REPLIES 9
Highlighted
Super User II
Super User II

Hi @Paulyeo11 ,

 

You can create new columns for YEAR and MONTH using DAX as follows:

 

yearCol = YEAR(tablename[Date])

monthCol = MONTH(tablename[Date])

 

Replace tablename[Date] in the above DAX expressions with your table name and date column name.

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

Hi Miss ,  Can you share how to start DAX ?

Highlighted
Super User IV
Super User IV

@Paulyeo11 ,

 

year = right([column],4)

 

Month =
var _pos = search("/",[column],,0)
return
mid([column],_pos+1,2)

 

new date =
var _pos = search("/",[column],,0)
return
date(right([column],4), mid([column],_pos+1,2), left([column],_pos-1))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver I
Resolver I

@Paulyeo11 Duplicate the date column (twice) in Power Query editor and transform to Year, Month no.. As Power Query supports 2 columns to merge at once, first merge would be on Year and Month no. and next merge will be of resultant column (year,monthno.) with Date.

Please mind the sequence you select the columns to merge.

Highlighted

Hi @Paulyeo11 ,

 

You don't have to start DAX. It's in Power BI only. Just see the GIF below on how you can create a dax calculation:

colCreation.gif

 

Check the following link to go through starting with DAX in Power BI:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-quickstart-learn-dax-basics

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

Hi Amit

I get error msg :-

Paulyeo11_0-1601971807134.png

 

Highlighted

Hi Miss 

Thank you for sharing the link . Now more lady start to like data visulisation. it use to be for man only.

Paul Yeo

 

Highlighted
Resolver II
Resolver II

@Paulyeo11 @Pragati11 @PoojaShri  @amitchandak 

You Have to create a new column .

Write below formula 

New Date = Format(table1[date], "YYYY.mm.dd")

This code is more efficiant  and let you modify your date as per your requirement .

You will get the desired result .

Give kudos if is it work for you .


Highlighted

@Paulyeo11 That statement would have been inappropriate back in 1960, and it is very inappropriate today. I think you want to apologize to @Pragati11 .

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors