changing text to date format gives completely different number

[Anonymous]

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")

Attached is the value returned. 

[Ashish_Mathur · Accepted solution]

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

[v-chuncz-msft · Accepted solution]

@Anonymous ,

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" )

[Ashish_Mathur]

Hi,

The attached screenshot should say it all

Hope this helps.

[Anonymous]

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 ?

[Ashish_Mathur · Accepted solution]

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

[Mariusz]

Hi @Anonymous 

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

[Anonymous]

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!