Change date format to dd/MM/yyyy in SSAS

[rajulshah]

Hello,

I want my date columns in dd/MM/yyyy format in SSAS.

As I format the column using the following dax, it's data type is changed to "Text".

Date = FORMAT(Date[dates],"dd/MM/yyyy")

But, I am unable to filter and sort after doing the above transformation.

Please guide me further on how to have format as dd/MM/yyyy and also should be filtered and sort by date.

Thanks in advance.

[mtauriza]

 If you are using a live connection of SSAS, you must set a sorting column from your tabular model properties first. In VS you can find that option in this panel when the column is selected. That should work.

[Nishantjain]

@rajulshah 

What format is the date coming out currently? When you use the function "Format", it will given you the results in "text format" and not "date format" and will not be treated as "date". 

Is your date coming out as "mm/dd/yyyy"? if so, can you check if what the data type is?

[rajulshah]

Hello @Nishantjain ,

Yes, by default column datatype is date with format "MM/dd/yyyy". SSAS doesn't give any option to change the datatype to "dd/MM/yyyy". If I use the format function, it has datatype as text and if I change the datatype of this formatted column, the format changes back to "MM/dd/yyyy".

[Anonymous]

i'm trying to change date format to dd/mm/yy in SSAS using below expression and it works fine, but its considering it as text when i'm try chang the data type automatically its bringing back to mm/dd/yy 

=FORMAT(DATE(date),"DD/MM/YY")

if i keep in text format and use it in power BI desktop while sorting its going to be big problem, it wont sort as expected

anybody could help me on this please 

[rajulshah]

Hello @Anonymous ,

You can set the original date column in "Sort By Column" properties of the formatted column.

You can get to column settings by selecting the column and pressing F4.

Hope this helps.

[Nishantjain]

@rajulshah 

I think you have got an issue with the locale of your settings. Check this article

Regional Settings

Power BI uses the regional settings to determine the format in which the dates are shown. If Power BI is recognising the data in "mm/dd/yyyy" format, it knows it is a date and then uses the regional settings to display in this format. 

Change your setting to see if that makes any difference

Thanks
Nishant

[rajulshah]

Hello @Nishantjain ,

We cannot change the regional settings in Power BI Desktop because it uses Live Connection. Please see below:

Please guide further.

Thanks.

[Nishantjain]

Got it. I don't think you can change the formatting in live connection See the link below 

Formatting SSAS values with a Live connection

You can also vote this idea

Better support for SSAS MD formatting

[rajulshah]

Hello,

I want my date columns in dd/MM/yyyy format in SSAS.

As I format the column using the following dax, it's data type is changed to "Text".

Date = FORMAT(Date[dates],"dd/MM/yyyy")

But, I am unable to filter and sort after doing the above transformation.

Please guide me further on how to have format as dd/MM/yyyy and also should be filtered and sort by date.

Thanks in advance.

[v-piga-msft]

Hi @rajulshah ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[rajulshah]

Hello @v-piga-msft ,

I have not yet found the solution to my problem yet.

[equilibrium]

Have you tried this approach provided by 

http://cloudbi.com.au/date-custom-format-in-ssas-tabular/ ?

I know the topic is of March 2019, but that solution helpded me to format date as needed not converting to text and also kepping the sort functionality on the date column.

[tarunsingla]

Try changing the Datatype of the newly calculated column to Date (from Modeling tab).

[v-piga-msft]

Hi @rajulshah ,

You could have a try if you sort the date column which is format as "dd/mm/yyyy" by the original date column, will it achieve your desired output.

Best Regards,

Cherry

[rajulshah]

Hello @v-piga-msft ,

We are fetching data from SSAS, hence this option wouldn't help in solving my issue.