Forum Discussion

MarkH's avatar
MarkH
Icon for Helper I rankHelper I
2 years ago
Solved

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

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
 
 
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi, MarkH 

    use column tools 

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

     

     

    • MarkH's avatar
      MarkH
      Icon for Helper I rankHelper I
      2 years ago

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

      wierd 😫

  • MarkH's avatar
    MarkH
    Icon for Helper I rankHelper I
    2 years ago

    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") )
    )
  • MarkH's avatar
    MarkH
    Icon for Helper I rankHelper I
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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