Forum Discussion

Marco_RC's avatar
Marco_RC
Copper Contributor
Nov 02, 2023

Language of month's name extracted from a date in a dynamic table from Power Pivot Data Model

Hi,   I have a problem with an Excel file that has been created in Windows 10 Pro with en-US regional format. The problem is that when I open it on a computer with Windows 10 Pro with es-MX regiona...
BI & Data Analysis
excel
Formulas and Functions
office 365
power bi
Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 15, 2023

Hi Marco_RC 

 

I think I can replicate what you explained, though I'm not sure where the issue comes from... I had similar challenges in the paste and instead of wastihg time investigating the reason I built my own Calendar/Date table with Power Query (I can provide the query)

 

Fixing your particular workbook (get months in Spanish) should be doable. However, depending on how the data loads to the Data Model the approach might be different. If you could share your workbook that would be ideal...

 

An improssible quick-fix would have been:

but the 3rd argument (locale_name) of the DAX https://learn.microsoft.com/en-us/dax/format-function-dax function is currently supported in PowerBI only 

  • Marco_RC's avatar
    Marco_RC
    Copper Contributor
    Nov 17, 2023

    Hi Lorenzo ,

     

    Thank you very much for the answer.

     

    We used a similar approach through Power Query to resolve the problem. However, I'm sharing with you the sample workbook and I would like to know the details of your proposed solution, please.

    Also we saw the format parameter on the MONTH dax function, and it is quite absurd that it is not supported in Excel, it would help a lot.

     

    My concern is that, on a Pivot Table, when the user add a date type field, Excel automatically generate the conversion of the month and it will show the wrong value, so it is really misleading.

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 05, 2023

      Hi Marco_RC 

       

      I thought you would provide (any kind of) feedback on what you asked to be shared

      Maybe you're too busy these days...

      • Marco_RC's avatar
        Marco_RC
        Copper Contributor
        Dec 06, 2023

        Hi Lorenzo ,

         

        I'm so sorry I couldn't follow up yet, but it's on my to-do list. I'll let you know as soon as possible.

         

        Thanks,

        Marco

         

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Nov 18, 2023

      Hi Marco_RC 

       

      The "solution" I put together a couple of years ago is attached (zipped as this site doesn't accept .xltx)
      Let me know if anything isn't clear or if you have questions

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Nov 18, 2023

      Hi Marco_RC 

       

      For your workbook created on Windows 10 US Pro with en-US regional format, just in case you did not think about it...

      Create on a sheet you'll hide later:


      For clarity let's assume the table that holds the month names in English in called Facts
      - Load MonthsES to the Data Model
      - Create a relationship between MonthsES (on [ID]) and Facts (field is probably [date (Month Index)]
      - On Facts add a calculated column [Month] with formula: =RELATED(MonthsES[Month])
      - Hide column [date (Month)] from the Client Tools
      - Remove [date (Month)] from your Pivot
      - Add [Month] instead

      (Implemented in attached file)

       

      BTW about DAX FORMAT function, forgot to mention I also tried the following some time ago. No error is raised but that doesn't have the expected effect: =FORMAT([date], "[$-es-MX]MMM")

Resources