How to transform period format ( JUL 2020) to format "2020007"?

[kimlien3006]

Hi everybody, 

I hope you can help me to solve this issue. I've tried several ways but did not work. 

So I have a Period table with format: JAN 2020, FEB 2020, .... However, I would like to have this column with format: 2020001 for JAN 2020, 2020002 for FEB 2020. 

Any idea will be appreciated. 

Thank you so much! 

Best regards, 

Lien

[themistoklis]

@kimlien3006 

Try the following formula if the field is text and not date

New_Date = 
RIGHT(Table[DateToConvert],4) & SWITCH ( LEFT(Table[DateToConvert],3), "JAN", "01", "FEB", "02", "MAR", "03", "APR", "04" )

[kimlien3006]

Thanks @themistoklis  for the quick solution. However, I have data for the whole year. It seems your formula works for 4 months only? 

[themistoklis · Accepted solution]

@kimlien3006 

Please see complete formula. Make sure though the field is text and not date format

New_Date =
RIGHT(Table[DateToConvert],4) &
SWITCH ( LEFT(Table[DateToConvert],3), "JAN", "01", "FEB", "02", "MAR", "03", "APR", "04", "MAY", "05", "JUN", "06", "JUL", "07", "AUG", "08", "SEP", "09", "OCT", "10", "NOV", "11", "DEC", "12" )

[amitchandak]

@kimlien3006 , Create a new column like this

date = "01 " & [Month year]  // Change data type to date

Now a new column

output = format([date],"YYYYMM")

or

output = format([date],"YYYY0MM")

[kimlien3006]

Thanks @amitchandak  for the quick solution. I tried and the output column does not work. Do you have ideas how I could do it wrong. 

[ryan_mayu]

@kimlien3006 

are you able to switch the Text column to Date?

if it works, then you can create a new column.

Column = year('Table'[Column1])*100+month('Table'[Column1])