Forum Discussion
Data Type Change
My table has a column of type text. It stores the name of the month abbreviated and current year hyphenated.
Under column tables tab, once I click the field, can I change the Data Type to Date? Or will this cause internal problems?
moe_elbohsaly
It depends on how that column has been used in your model in various calculations. As per your description, it is a text field, the best practice is to creat a dates table and link it to your fact table. you create a dates table with the following"Dates = ADDCOLUMNS( CALENDAR("01/01/2021","31/12/2021"), "Month No" , MONTH([Date]), "Month Name" , FORMAT( [Date] , "Mmmm" ), "Year" , YEAR([Date]), "Month Year No" , (YEAR([Date]) * 100) + MONTH ([Date]), "Month Year" , FORMAT( [Date] , "Mmm yyyy"), "Quarter" , QUARTER([Date]), "Year Qtr" , FORMAT( [Date] , "YYYY \QQ"), "Week Day" , WEEKDAY([Date],2), "Week" , FORMAT( [Date] , "Dddd" ) ).
2 Replies
moe_elbohsaly
It depends on how that column has been used in your model in various calculations. As per your description, it is a text field, the best practice is to creat a dates table and link it to your fact table. you create a dates table with the following"Dates = ADDCOLUMNS( CALENDAR("01/01/2021","31/12/2021"), "Month No" , MONTH([Date]), "Month Name" , FORMAT( [Date] , "Mmmm" ), "Year" , YEAR([Date]), "Month Year No" , (YEAR([Date]) * 100) + MONTH ([Date]), "Month Year" , FORMAT( [Date] , "Mmm yyyy"), "Quarter" , QUARTER([Date]), "Year Qtr" , FORMAT( [Date] , "YYYY \QQ"), "Week Day" , WEEKDAY([Date],2), "Week" , FORMAT( [Date] , "Dddd" ) ).
Thanks Fowmy ! I managed to do a DAX namely FORMAT([text_column],"MMM-YYYY") since my text contains the month name abbreviated and current year hyphenated.
