Forum Discussion
datevalue from text to number
Hi,
I need to convert the months from text to a number, i have the names of the month in a column but need these to be shown as number. I am trying to use datevalue to convert the month from January to 01/January/2019. How could I combine the data from the column and change it to a number/date.
I have tried the following formula but get an error message.
hi pardeepd84
Try this measure:
Convert to date = DATEVALUE("01/"&FIRSTNONBLANK('Table'[Month Name],"")&"/2019")the output will be as below:
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
Hi pardeepd84 ,
Based on my test, you could use the following formula to create a Date column or a Month number column:
Date= DATEVALUE("01-"&[Month Name]&"-2019")Month Number = MONTH( DATEVALUE("01-"&[Month Name]&"-2019") )And then change the Format as you expect:
Refer to:
DATEVALUE function (DAX) - DAX | Microsoft Docs
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
hi pardeepd84
Try this measure:
Convert to date = DATEVALUE("01/"&FIRSTNONBLANK('Table'[Month Name],"")&"/2019")the output will be as below:
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
HI,
Hi I still get the same error message.
Can you share sample of your data in the table format here (not image), or share your PBI file?
pardeepd84 You could try this:
MonthAsNum = (search(left('Table'[MonthName],3), "JanFebMarAprMayJunJulAugSepOctNovDec",1,-2)+2)/3Hi,
Try this calculated column formula
Date = 1*("01/"&Data[Month Name]&"/2019")
Format this as a Date.
Hi pardeepd84 ,
Based on my test, you could use the following formula to create a Date column or a Month number column:
Date= DATEVALUE("01-"&[Month Name]&"-2019")Month Number = MONTH( DATEVALUE("01-"&[Month Name]&"-2019") )And then change the Format as you expect:
Refer to:
DATEVALUE function (DAX) - DAX | Microsoft Docs
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
