Date Custom Format in SSAS Tabular

By in
9154
Date Custom Format in SSAS Tabular

 

Some days ago I been asked by a BI developer  about how to set format of SSAS Tabular date column to dd/mm/yyyy in SSDT(Sql Server Data Tools). If you look at the Properties of  a data column in your model will see General has been set as default for Data Format property:

 

By opening the drop-down you will see some predefined formats but the point is this list shows different items based on the local setting on your machine. If you use US English the list has a complete set of formats based on m/d/y:



But if you use UK or Australian English the available formats are more related to d/m/y:

 

And it doesn’t allow you  to enter any new format outside of this list. For example if the language is AU you can’t set the format to MM/dd/yyyy even when you try to type after pressing Enter key the value would change back to General.

I found some people have suggested to create a calculated text column by  and use Format function:

DateText=Format(DimDate[Date],"MM/dd/yyyy")

 

This sorts the problem out but it is better to set the format on the main column instead of duplicating it in another text column. You may know  in SSAS Tabular  the definition of all objects is kept in one JSON file (same as the XML file in SSAS Multidimensional) named Model.bim as default and you can edit this file and change any attribute in the model:



After viewing the code, find the date column and change  the value of formatString node to the desired format and then close and save the model:

  • Add the full line if formatString doesn’t exist in the block.  

Now when opening the model in the design mode you can see the value of Data Format has been changed to Custom and Format String to MM/dd/yyyy:

Summary

In SSAS Tabular project in SSDT the list of predefined date formats are different based on the English local has been used on the machine. To set format of a date column to any value outside of the list, Model.bim file need to be edited manually  and value of formatString node be modified to the desired format.

Leave a reply

Your email address will not be published. Required fields are marked *