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

[BerniceS]

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

.menu doesn't contain correct format

[v-huizhn-msft]

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.

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]

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.