cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Text to Year gives wrong output

So I have some data broken down by Month, Quarter, and Year. I was able to format the Month correctly as a date, but the Year is being tricky. For example: when I make it Date/Time, 2015 turns into 7/7/1905. When I switch to yyyy format, it just shows 1905. How can I get it to recognize 2015 as the correct year?

1 ACCEPTED SOLUTION
BhaveshPatel
Community Champion
Community Champion

Hi There,

 

You should use & operator to create a date column and change the data type to date  and format as yyyy to achieve the results.

 

Datecolumn = "1/" &"1/" &[Year]

 

Convert the data type to the date and let me know how it goes or need further help.

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

4 REPLIES 4
BhaveshPatel
Community Champion
Community Champion

Hi There,

 

You should use & operator to create a date column and change the data type to date  and format as yyyy to achieve the results.

 

Datecolumn = "1/" &"1/" &[Year]

 

Convert the data type to the date and let me know how it goes or need further help.

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Anonymous
Not applicable

@BhaveshPatel

 

Thank you

CahabaData
Memorable Member
Memorable Member

so the Year field needs to be a Number field type, not a Date field type.  that's just the way it is..... or based on the title of your post you can force it to be a text field using the ribbon modeling tab.

 

am not entirely clear what your goal is - one thought however may be to add another field that is a true date field ....so if your periods are months have an actual date field perhaps using the 1st day of the month.  I just throw that out there as a consideration but not really sure if it helps.....

 

 

 

www.CahabaData.com
Anonymous
Not applicable

I understand. My goal here is to look at month over month change as far as sales and margin go. I have that figured out by using PREVIOUSMONTH('Table'[Month]), where 'Table'[Month] is formatted as 01-MM-YYYY. Measures work great.

 

I also want to look at year over year change. Here i tried PREVIOUSYEAR('Table'[Month]), since I knew this has the year in it. This doesn't give me any calculations on my measures. I then figured I'd try to format the Year column as a date and use PREVIOUSYEAR on that, but it can't be formatted.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors