cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BerniceS Frequent Visitor
Frequent Visitor

Locale doesn't work on date format for table created in PBI

When I create a new table and I use the CALENDAR dax function my date gets created using my locale (cdn) and not the locale that I have set in PBI (US) for this data.  When I try to change the date so that it is mm/dd/yyyy, that value is not even available.  If the data gets pulled into the data model with the locale being set in the query I do not have this issue. Everything looks good.  This is the same issue I ran into using Excel Power Query and Excel Power Pivot.  If I do not have the correct format then wherever these dates are used they don't display correctly

.PowerBI Date Locale issue.pngmenu doesn't contain correct format

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: Locale doesn't work on date format for table created in PBI

Hi @BerniceS,

 

Based on my understanding, you load the date in extre file, and the locale(cdn) of the date in the file is different from locale(US) of Power BI destop? But I don't find the locale(cdn) in Power BI desktop as the highlighted in red line shown in screenshot below.

 

1.PNG
For your issue, it may be caused by the incompatibility of date type for different locale, Please change them to one locale before you use them. Here is a similar theard.

 

Best Regards,
Angelia

BerniceS Frequent Visitor
Frequent Visitor

Re: Locale doesn't work on date format for table created in PBI

Hi Angelia, Actually, that is not what I said.  You can see from my screen print that I am creating the date directly in PowerBI as a column using a DAX formula.   The data set that I am using is formatted as English(United States).  When I import data that has a date column, and I add as a step in the query Change Type (locale), the date works great.  I can format it exactly as I want.  However, using the add column method does not work regardless of whether I do or, do not create the column with the 'Regional Setting for This File' set to English(United States).  It always uses my locale which is English(Canada).  The option for the requested format is not even there and it should be.   I want and need MM/DD/YYYY not MM/DD/YY.

 

If you had a PC that you could set to a locale of English(Canada) and tested this out you would see what I am talking about.  It is an issue both in PowerBI and Excel's PowerQuey/PowerPivot.

 

There should either be a work around that works or a product fix that recognizes the locale for the data set and acts accordingly.

 

Thank you.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 311 members 3,181 guests
Please welcome our newest community members: