take substring and formate date in dax

[Anonymous]

I have a timestamp in powerbi in UK format (dd/mm/yyyy hh:mm:ss). 

I want to extract only the date in dd/mm/yyyy format. In DAX I used LEFT:

LEFT(column,10)

But it changed to mm/dd/yyyy format.

I tried 

FORMAT(LEFT(column,10),"dd/MM/yyyy"), but still have results in US format.

I tried https://community.powerbi.com/t5/Desktop/Format-Date-and-Time/m-p/248416#M110162 and https://community.powerbi.com/t5/Desktop/Date-Format/m-p/120348/highlight/true#M50858 to no avail

Any suggestions are most welcome

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

To create a calcualted column using the formula.

Column 3 = FORMAT('Table1'[date],"dd") &"/"&FORMAT('Table1'[date],"mm") & "/"& FORMAT('Table1'[date],"yyyy")

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

1.  In the Query Editor, split the column into Date and Time
2.  Right click on the Date column and go to Change Type > Locale > Date > Engligh (India) > OK

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

To create a calcualted column using the formula.

Column 3 = FORMAT('Table1'[date],"dd") &"/"&FORMAT('Table1'[date],"mm") & "/"& FORMAT('Table1'[date],"yyyy")

Regards,

Frank

[Gokul]

Add custom column like "Column 2 = 'Query'[date].[Date]" and change the data type to date and format under modelling tab or directly you can proceed with format under modelling