cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

changing text to date format gives completely different number

Hello,

This should be simple.  I have a column of values that are formatted as text that reads "201901", "201902", etc.  I am trying to change their value to 2019-01, but when I change the format to date, the values are changed to completely random numbers.  

Steps I've taken.  

-edit query

-add column: Year-Month= Format(Volume[calendar year month], "YYYY-MM")

date.PNG

Attached is the value returned. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: changing text to date format gives completely different number

Hi,

The first two rows will automatically appear.  For the third row, you simply have to go to Add column > Custom column and add everything you see from Date.From


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Community Support
Community Support

Re: changing text to date format gives completely different number

@erinwatkilace ,

 

You may use DAX below.

Year-Month =
VAR d =
    DATE ( QUOTIENT ( Volume[calendar year month], 100 ), MOD ( Volume[calendar year month], 100 ), 1 )
RETURN
    FORMAT ( d, "YYYY-MM" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: changing text to date format gives completely different number

Hi @erinwatkilace 

You can use the below DAX patern.

Column = FORMAT('Calendar'[yearWeekNo],"")

Or if you use Query Editor M code is

Number.ToText( your number column )

hope this helps
Mariusz

Highlighted
Super User IV
Super User IV

Re: changing text to date format gives completely different number

Hi,

The attached screenshot should say it all

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: changing text to date format gives completely different number

Hello,

Thanks for the help.  I entered the measure above, then tried to change to Date formula and recieved #ERROR.

this is what I entered:

Calendar new type = FORMAT(Data_Volume[Calendar Year Month],"")
 
Then I went to date type, changed to date.  I recieved this error: Cannot convert value '201708' of type text to type date.
Any suggestions would be appreicated. thanks!
 
Highlighted
Frequent Visitor

Re: changing text to date format gives completely different number

Hi,

thanks for the help. 

I tried entering this in query editor, but it didn't work. I'm brand new to power bi so this is probably on me. 

would I enter this is custom column ?

Highlighted
Super User IV
Super User IV

Re: changing text to date format gives completely different number

Hi,

The first two rows will automatically appear.  For the third row, you simply have to go to Add column > Custom column and add everything you see from Date.From


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Community Support
Community Support

Re: changing text to date format gives completely different number

@erinwatkilace ,

 

You may use DAX below.

Year-Month =
VAR d =
    DATE ( QUOTIENT ( Volume[calendar year month], 100 ), MOD ( Volume[calendar year month], 100 ), 1 )
RETURN
    FORMAT ( d, "YYYY-MM" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors