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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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