topic Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format. in DAX Commands and Tips

Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

MarkH — Wed, 20 Dec 2023 11:37:40 GMT

Here is the source data.

Just Source = DATATABLE (

"StartDate", DATETIME,

"EndDate", DATETIME,

{

{ "16/03/2023 09:00", "16/03/2023 17:30" },

{ "16/03/2023 14:00", "16/03/2023 17:30" },

{ "17/03/2023 09:30", "17/03/2023 12:30" },

{ "16/03/2023 09:00", "17/03/2023 17:00" },

{ "16/03/2023 09:00", "20/03/2023 17:00" },

{ "16/03/2023 09:00", "20/03/2023 12:30" },

{ "16/03/2023 14:30", "20/03/2023 12:30" },

{ "17/03/2023 14:30", "20/03/2023 12:30" },

{ "16/03/2023 09:00", "27/03/2023 17:00" },

{ "02/03/2023 09:00", "27/03/2023 17:00" }

}

)

Every thing is fine in the desktop except the very last StartDate. 02/03/2023

This actually shows as 03/02/2023. All the other dates return UK format just fine.

Testing shows that if the first 2 digits are 01 to 12 it converts to US format.

I know you can change the locale in Power query but this is pure DAX.

To be clear, my locale is UK.

Why is it not consistent at keeping UK format?

How to fix it please.

Thanks

Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

Dangar332 — Wed, 20 Dec 2023 12:15:35 GMT

Hi, MarkH

use column tools

in column tools choose format option and select dd/mm/yyyy or as you want

Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

MarkH — Wed, 20 Dec 2023 12:38:37 GMT

I had tried that & unfortunately it doesn't work.

wierd 😫

Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

MarkH — Wed, 20 Dec 2023 12:47:19 GMT

Fixed it by not using DATATABLE and forcing a format. (You cannot format using DATATABLE)

Source2 = UNION (

ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("16/03/2023 17:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 14:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("16/03/2023 17:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("17/03/2023 09:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("17/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("17/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 14:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("17/03/2023 14:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("27/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

ROW ( "StartDate", FORMAT("02/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("27/03/2023 17:00", "dd/mm/yyyy hh:mm") )

)

Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

MarkH — Wed, 20 Dec 2023 12:59:04 GMT

however, that creates the columns as text & when you convert them to datetime the original problem reappears 😪

Re: Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

Anonymous — Wed, 27 Dec 2023 09:59:16 GMT

Hi MarkH ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Using DAX to create a calculate table:

Just Source = DATATABLE ( "StartDate", DATETIME, "EndDate", DATETIME, { { "16/03/2023 09:00", "16/03/2023 17:30" }, { "16/03/2023 14:00", "16/03/2023 17:30" }, { "17/03/2023 09:30", "17/03/2023 12:30" }, { "16/03/2023 09:00", "17/03/2023 17:00" }, { "16/03/2023 09:00", "20/03/2023 17:00" }, { "16/03/2023 09:00", "20/03/2023 12:30" }, { "16/03/2023 14:30", "20/03/2023 12:30" }, { "17/03/2023 14:30", "20/03/2023 12:30" }, { "16/03/2023 09:00", "27/03/2023 17:00" }, { "02/03/2023 09:00", "27/03/2023 17:00" } } )

1.Select the field in the Modeling view, and then select the dropdown arrow under Format in the Properties pane.

2.Once you've selected Custom from the Format dropdown menu, choose from a list of firstformat.

3.Repeat the step1 and 2 and apply to another column

4.Final output

Best Regards,

Albert He