Forum Discussion

saunders's avatar
saunders
Helper II
9 years ago
Solved

Date format - Desktop vs. Service

Hi all,   I have dates formatted as dd/mm/yyyy in Desktop but when I publish to the service the format get changed to mm/dd/yyyy.  How can I force the service to respect my Australian date format a...
  • irmat's avatar
    irmat
    9 years ago

    saunders

    Hi,

    I had the same issue by using SSAS. I noticed that in SSAS the data on the data format on the date column was set to either General or short date. Setting the data format to dd/MM/yyyy fixed the issue.

    Hope this helps.

saunders's avatar
saunders
Helper II
9 years ago

Hi Simon, thanks for your reply.  I've investigated this more and it seems that, when there is a live connection to Analysis Services or when there is DirectQuery connection to SQL Server, PowerBI displays the US format, not the local format.

 

Is there anyone else who is experiencing this?

 

Thanks!

 

Analysis Service - Import (correct)

 

 

Analysis Service - Live Connection (incorrect)

 

 

 

SQL Server - DirectQuery (incorrect)

 

 

 

irmat's avatar
irmat
Advocate II
9 years ago

saunders

Hi,

I had the same issue by using SSAS. I noticed that in SSAS the data on the data format on the date column was set to either General or short date. Setting the data format to dd/MM/yyyy fixed the issue.

Hope this helps.

  • saunders's avatar
    saunders
    Helper II
    9 years ago

    Hey, thanks for the note, that fixed it.

  • LizHackett's avatar
    LizHackett
    Regular Visitor
    9 years ago

    Hi irmat,

    I had the same problem as saunders (in Eastern Australia as well) and I've changed my date format under Windows Region settings to the long format dd/mm/yyyy, but still having the same issue.

    Am I changing the date format in the wrong place?  If so, where should I be changing it?

     

    In Query editor I changed the type to Date (as it comes through as text via a web link to a dataset from Blackbaud CRM) and getting an error on those dates in which the day is above 12. 

     

     

     

     

     

     

     

     

     

     

     

    Thanks!

    • irmat's avatar
      irmat
      Advocate II
      9 years ago

      Hi LizHackett

      Have you maybe tried the below

      or changing it to English (Australia)? You will have to restart Power BI Desktop after this change.

       

       

      • LizHackett's avatar
        LizHackett
        Regular Visitor
        9 years ago

        Hi irmat

        Thanks!! The first solution you suggested to change the date format under Modelling within Desktop  (not in query editor) to dd/mm/yyyy worked :)
        I made sure I had my location in Desktop set to Australia.

        Thanks for your help!

    • mmmg's avatar
      mmmg
      Frequent Visitor
      6 years ago

      Hi LizHackett,

      you're on the right track. I tried all of the changing regional settings and changing the date settings via the Modelling menu in Power BI. None of them worked. I then tried changing the Data Type of my date fields via the Query Editor icon in the Home ribbon. I needed to do this because my date fields are varchars in my source table. Once I did this I clicked on Close and Apply, saved my report and then published - the dates are now staying as dd/mm/yyyy (I'm in Sydney). So the problem all along was that my date fields were varchars. Hope this helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for the info. Its solved same issue we had in our project :-)

  • JESLIN's avatar
    JESLIN
    New Member
    2 years ago

    Hi,

    I am located in Australia ,  and have changed all setting to English(Australia) - computer/Edge/Chorme/Power BI.   The Date formate in Desktop and All shown in Edge are accurate in Australia mode.  But the date formate is still in US mode in Google Chrome even the setting is in Australia mode.   could you advise any setting need to change if BI report is shown in Chrome ?