Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Issue regarding Calculated Column

Hi All, I have tried to get the date value as mm/dd/yyyy from "SEP2118" value which is in text format. Tried to change the data type but not able to do so, Please help me asap. Thanks in advan...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    AFAIK, dax formula has datevalue function which can convert text value to date but it only support convert text format date value.

     

    According to your snapshot, I found your filed values seems not same as valid text format date values, I'd like to suggest you do some additional steps to use datevalue to convert original text column.

    Column = DATEVALUE(LEFT([MonthYear],3)&"/"&RIGHT([MonthYear],4))

     

    Regards,

    Xiaoxin Sheng

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 years ago

Anonymous

 

Have you tried to change the data type in 'Editing Quires'? It works on my end. Thanks.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

ryan_mayu,

Below specified date value is coming from a field, have created a calculated column to get the required part like "Sep1218", after that tried to change the data type and does not work for me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous,

     

    AFAIK, dax formula has datevalue function which can convert text value to date but it only support convert text format date value.

     

    According to your snapshot, I found your filed values seems not same as valid text format date values, I'd like to suggest you do some additional steps to use datevalue to convert original text column.

    Column = DATEVALUE(LEFT([MonthYear],3)&"/"&RIGHT([MonthYear],4))

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Guru,

      We have Data in the form of "SimplJan3118sert" ,
      where it contains Month(Highlighted in Black),Date(In Red) then finally Year(In Purple)
      and i am not able to convert it to Date type as your input.

      Anyway thanks for the reply!!!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    Anonymous

     

    Please try to create another column to transform the data. Hope this is helpful.Thanks.

     

    DATE = 
VAR Y=RIGHT(Sheet7[Column1],4)
VAR M=LEFT(Sheet7[Column1],3)
VAR MONTHNUMBER=SWITCH(M,"JAN",1,"FEB",2,"MAR",3,"APR",4,"MAY",5,"JUN",6,"JUL",7,"AUG",8,"SEP",9,"OCT",10,"NOV",11,"DEC",12)
RETURN DATE(Y,MONTHNUMBER,01)